VBA code for changing formula reference inside cell

[redpoll]

Hi,

I have a Sheet1 where i.e. Column C is January, Column D is February etc. and different rows include different expense categories during that specific month.

I have a summary table on Sheet2 that I update each month which combines all the information I need. At the moment I have to update cell references on Sheet2 each month and am looking for a VBA code to do this task for me.

My question is, when the month is over, how can I change i.e. "=Budget!AK25" on cell A1 to "=Budget!AL25" using VBA code so that i could run it each month and the reference would be changed one column to the right.

Thanks for your help in advance!

 

[Mike LH]

Hi,

What you're asking is doable but there are easier ways. Try this


=OFFSET(Budget!AK25,0,A1)

If A1 contains 0 (Zero) the formula returns AK25 but enter a 1 and it returns AL25 etc.

 

[redpoll]

Thank you, this is a good work-around.