Dec 21 2021 04:18 PM
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?
Dec 21 2021 11:52 PM
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.
Dec 27 2021 08:40 PM
Hi @Martin_Weiss , 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.
Dec 29 2021 02:14 AM
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
Dec 29 2021 09:08 AM
Hi @Martin_Weiss ,
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?
Dec 30 2021 01:27 AM
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.
Dec 30 2021 05:15 AM