Dynamic formula's help please

[Lewster]

I have created a dashboard (albiet a bit rough), I would like what I suspect would be dynamic formula to fetch data on a daily and year to date basis, this will eventually come from different workbooks. I would like the daily cell to pick up the last entry only (up to row 1002) and update my data table but also update the year to date cell in the table with the year to date data either from the daily entries or week to date entries.

Thanks in advance.

Workbook attached. See data tab.

[TMS]

Maybe:

Please Login or Register  to view this content.

Regards, TMS

[Lewster]

That picks up the last entry for the daily cell so many thanks with that but what would work for the rolling year to date average ?? and could you please explain the formula so I can understand it please.

[TMS]

You said you wanted the last number in column L to go in cell B3. That's what you got. If you want the last number in a different column, lust change every occurrence of the column letter.

The formula is matching an arbitrary large number, which it won't find, so it returns the next best thing. The match value is then used to INDEX the column to return the last numeric value.

Regards, TMS

[Lewster]

I also asked for " also update the year to date cell in the table with the year to date data either from the daily entries or week to date entries." So this puts the average from either the daily or weekly to the year to date cell in table.

[TMS]

. I've shown you how to create a dynamic formula that returns the last numeric value in the column. I would suggest you try to apply the same technique elsewhere. Your dashboard looks very good but I don't have time to spend working out where things are and how to get them where you want them to be.

You clearly state that the value in B3 is the last value in column L. And that you would have similar formulae for C3:I3. Logically, the data will come from columns M onwards so that you can drag the formula across. However, you have week to date figures where they will go. On the basis that column M has week to date figures in it, you could create a dynamic formula for week to date figures. But you don't explain how you calculate, or want to calculate year to date figures, or where they sit in the worksheet. Hence you don't get a dynamic formula for rolling year to date figures.

If the rolling year to date figure is the cumulative values in column L, you could use:

Please Login or Register  to view this content.

dragged down.


Regards, TMS

[Lewster]

Thank you for your help, got it all worked out now.

[TMS]

You're welcome. Thanks for the rep.