I have a complex spreadsheet with an Excel table containing my data and formula [in Excel 2010]
I have a series of columns by month. eg. Jan x, Jan y, Jan z, Jan Total, Feb x, Feb y, Feb z, Feb total, etc (thru to end of year).
I have a YTD formula that is driven by a drop-down (where I select the current month) = eg. Feb z YTD (where the formula needs to pick up Sum(Jan z, Feb z).
From scanning the web, I've plumped for an evaluate formulae. So I have a fixed cell which in my above example, does a vlookup against my selected month to retrieve the formula I need - in my example I return the text (eg, into cell $D$5):
SUM(DATA[@[JAN z]],DATA[@[FEB z]])
In my YTD z column, I have the formula:
= eval($D$5)
with the following code in VBA:
Function eval(r As Range) As Variant
eval = Evaluate(r.Value)
End Function
The formula 'works' but it does not auto-update, and if any of the numbers change, I need to force the update by either hitting return within the cell, or replacing the entire column with the formula.
I have my formula on auto-update but can't figure out how to make this dynamic.
Any ideas from the power users? The only alternative I can think of is write a huge IF statement (IF my current month = Jan, = Jan z, If Feb, Sum(Jan z, Feb z) etc....
Any help would be hugely appreciated.
Cheers
I have a series of columns by month. eg. Jan x, Jan y, Jan z, Jan Total, Feb x, Feb y, Feb z, Feb total, etc (thru to end of year).
I have a YTD formula that is driven by a drop-down (where I select the current month) = eg. Feb z YTD (where the formula needs to pick up Sum(Jan z, Feb z).
From scanning the web, I've plumped for an evaluate formulae. So I have a fixed cell which in my above example, does a vlookup against my selected month to retrieve the formula I need - in my example I return the text (eg, into cell $D$5):
SUM(DATA[@[JAN z]],DATA[@[FEB z]])
In my YTD z column, I have the formula:
= eval($D$5)
with the following code in VBA:
Function eval(r As Range) As Variant
eval = Evaluate(r.Value)
End Function
The formula 'works' but it does not auto-update, and if any of the numbers change, I need to force the update by either hitting return within the cell, or replacing the entire column with the formula.
I have my formula on auto-update but can't figure out how to make this dynamic.
Any ideas from the power users? The only alternative I can think of is write a huge IF statement (IF my current month = Jan, = Jan z, If Feb, Sum(Jan z, Feb z) etc....
Any help would be hugely appreciated.
Cheers