Hopefully someone can steer me in the right direction as I'm trying to wrap my head around how to approach this.
We have two teams working on separate components of a project, using the same core data for different but related tasks. The core data will be updated as the projects progress (new items being added, status of the item being changed and information being updated as it is validated). Each project then has a whole heap of information unique to that project for doing calculations and tracking progress of each item.
What I need to be able to do is ensure that both spreadsheets are working off the same core data but can manipulate their own information.
I'm heading towards the idea that I need to put all of the core information into a data source and use a match/index and a macro (each item has a unique identifer) to push it into two project s.sheets? And then use the unique ID to lookup the status changes in the project s/sheets to reflect in the data source the dependent items for the other project to use?
So basically, I'd end up with:
Datasource workbook: holding the core item data, which is entered directly, and then status columns that are updated by vlookup from the project w.books (these would be like if project A has X value in col X status is "Yes").
Project A workbook: core item data updated via match/macro and team A can use to update the information unique to the project.
Project B workbook: as above for team B.
Because this means that new items will need to be added, how do I get those to be reflected in the project workbooks without affecting the additional columns & calculations that are unique to that project? And is there a way to then flag which cells/rows have values that have been updated?
Oh, and just to make things difficult there are 1300 unique items!
Hopefully this makes sense my head is a mess today!
We have two teams working on separate components of a project, using the same core data for different but related tasks. The core data will be updated as the projects progress (new items being added, status of the item being changed and information being updated as it is validated). Each project then has a whole heap of information unique to that project for doing calculations and tracking progress of each item.
What I need to be able to do is ensure that both spreadsheets are working off the same core data but can manipulate their own information.
I'm heading towards the idea that I need to put all of the core information into a data source and use a match/index and a macro (each item has a unique identifer) to push it into two project s.sheets? And then use the unique ID to lookup the status changes in the project s/sheets to reflect in the data source the dependent items for the other project to use?
So basically, I'd end up with:
Datasource workbook: holding the core item data, which is entered directly, and then status columns that are updated by vlookup from the project w.books (these would be like if project A has X value in col X status is "Yes").
Project A workbook: core item data updated via match/macro and team A can use to update the information unique to the project.
Project B workbook: as above for team B.
Because this means that new items will need to be added, how do I get those to be reflected in the project workbooks without affecting the additional columns & calculations that are unique to that project? And is there a way to then flag which cells/rows have values that have been updated?
Oh, and just to make things difficult there are 1300 unique items!
Hopefully this makes sense my head is a mess today!
