john_potter
New Member
- Joined
- Mar 9, 2016
- Messages
- 3
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 arentover-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 todays 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.
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 arentover-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 todays 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.
