VLOOKUP for data that changes daily

[asajnani]

Hi,

I have a dashboard that I've created using PowerPivot (OLAP cube).

The dashboard updates daily to show current patients who are in hospital [today] and how many of them are at risk of readmission. This calculate a percentage of how many people are at risk.

What I want to do is track on a daily basis what the percentage of risk is so that I can chart it for the dashboard ie:

21-Nov 1%
22-Nov 1.01%
23-Nov 0.98%
etc

I thought to use a VLOOKUP on a separate worksheet to say if the date matches then put the correlating percentage. However, I realised that this would not store data for previous dates as the date on the dashboard changes daily which would then cause an error.

Anyone have any nifty ideas of how to get around this?


Thanks,
A

 

[r1c1]

This is not possible with formulas. (you may be able to write some circular reference to work it out, but it is not worth it).

You need to have a macro that can archive the values everyday (or everytime you open the dashboard and press some button).

The basic approach is like this:

• After the dashboard is refreshed run a small macro automatically (you can tie this to refresh event or a button)
• In this macro, calculate the daily risk rate for current date and append it at the end of daily risks table

Feed the daily risks table to your chart.