Tracking historical values: Index/Match using VBA?

[john_potter]

Good morning all.

As always, thanks in advance for your time...

I need to capture project status for a list of projects over time and keep historical record of those statuses.

I currently have a data connection from a sharepoint listthat brings in a list of projects and their statuses (along with other data) thatis refreshed every time the spreadsheet is opened. What I need to do is capturethe project status (Red/Yellow/Green) for each project when I open thespreadsheet (opened on a random schedule). This is relatively easy if I want asnapshot in time, but tricky to save historical statuses that aren’tover-written through regular code. I know I could reduce the calculationiterations so that each cell is only calculated once but have a load of othercalculations that need to fire. I thought about using VBA to do an index match(using project code and date) – am I correct that the VBA will only work if bothcondition are found, i.e. it matches on both project code and today’s date,then inserts that value into another table that has project code vertically anddates (each day of the year) horizontally – BUT will values in other cellsremain intact (hence maintaining a historical record of status, by project forwhatever day the spreadsheet was opened).

I have spent the morning looking for solutions online andsearched this forum, but am not experienced in VBA – if someone could point mein the direction where I can teach myself, or better yet have some code intheir back pocket, it would be hugely appreciated.


Thanks.