IF or Vlookup

New Contributor

I'm working on a personal budget. I made an IF Statement with a vlookup nested to retrieve the data but as I change the month, the data disappears (which makes sense) but how do I make the data stay as I move month to month?

6 Replies

Hi @Mecii101 

 

please upload an example file with some test data, otherwise it will be impossible for us to give you a proper advice in this specific case.

Hi @DTE , silly me! 

 

With the holiday rush, I haven't looked at my emails. I hope it's not too late. Please see the attached file. 

 

Personal Budget.xlsx

Hi @Mecii101,

 

the template that you are using is just not designed to dynamically maintain the budgets for different months, as there is only one single month in the Budget sheet available.

 

With this structure, the only way to keep the budget for a specific month is to copy the formulas and paste only the values:

1. Enter the budget figures for January in the Budget sheet

2. Go to the YTD Budget sheet, select all formulas for JAN, copy (Strg+C) and paste special | paste values. As there are combined cells in rows 24, 33, 41 and so on, you need to do this in blocks.

3. Enter the budget figures for February in the Budget sheet and continue with copy & paste 

 

 

Hi @DTE ,

Each month column has the same formula. The IF  statement is linked to the Budget worksheet. The month will move to the corresponding month on the YTD budget worksheet when I change it. However, I wondered if there is a way to keep the information I put in January when I change the month. Because once I change the month, the information in January clears to input the following month's data. 

 

Is there a way to keep data, or would I have to remove the formula and leave the values each time I change the month?

Hi @Mecii101 

 

yes, I completely understood the issue. And as I explained, in my eyes the only solution is to remove the formula after each month and replace it with the values.

I agree, this is not a very clever way to work with Excel, but that's how your template is designed. To be more flexible, you could copy the budget worksheet for every month and adjust the formulas in the YTD sheet, so it points to the appropriate budget worksheet.

Hi @DTE
I know understand what you are explaining to me. Thank you for all your help. I really appreciate it!

Have a happy new year