IF or Vlookup

Question

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?

martin_weiss · Answer

Hi&nbsp;Mecii101&nbsp;&nbsp;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.

mecii101 · Answer

Hi&nbsp;Martin_Weiss&nbsp;, silly me!&nbsp;&nbsp;With the holiday rush, I haven't looked at my emails. I hope it's not too late. Please see the attached file.&nbsp;&nbsp;https://1drv.ms/x/s!ApgqzVIjUybtgtZEeOIzpWzfTGONLA?e=j8aC5r

martin_weiss · Answer

Hi&nbsp;Mecii101,&nbsp;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.&nbsp;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 sheet2. 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 &amp; paste&nbsp;&nbsp;&nbsp;

mecii101 · Answer

Hi&nbsp;Martin_Weiss&nbsp;,Each month column has the same formula. The IF&nbsp; 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.&nbsp;&nbsp;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?

martin_weiss · Answer

Hi&nbsp;Mecii101&nbsp;&nbsp;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.

Forum Discussion

IF or Vlookup

6 Replies

Resources