Hello, Experts.
Novice here. Seeking advice. I have a workbookcontaining many spreadsheets pertaining to data pulled from many locations/sites. This data is sorted by site as well as by the manager responsible for the site. And each manager has several sites.
Each spreadsheet shows each individual site info as wellas collective info for each manager area.
And manager are reassigned sites several times a year. That’s my issue. Making the manager/site changes.
Currently we use the old vlookup and match index to pull data into each sheet. Then each sheet has instructions on which rows belong to which manager etc. As manager areas change, each sheet then has to be updated accordingly, one cell at a time. Surely there is some other way to update the sites tied to each manager.
Is it possible to create a 'shortcut' that would represent each area and could be used as the instructions for totaling each manager area. Something that says - for every computation on this sheet that refers to Bob's area, look at the variable sheet to see which sites are Bob's, then look to the site info on that sheet andcompute. Then on all other sheets, Bob formulas would look to the variable sheet, then back, compute...
I guess it would work off of site names to match or all sites would be on same row numbers.
Thank you very much for all feedback.
Novice here. Seeking advice. I have a workbookcontaining many spreadsheets pertaining to data pulled from many locations/sites. This data is sorted by site as well as by the manager responsible for the site. And each manager has several sites.
Each spreadsheet shows each individual site info as wellas collective info for each manager area.
And manager are reassigned sites several times a year. That’s my issue. Making the manager/site changes.
Currently we use the old vlookup and match index to pull data into each sheet. Then each sheet has instructions on which rows belong to which manager etc. As manager areas change, each sheet then has to be updated accordingly, one cell at a time. Surely there is some other way to update the sites tied to each manager.
Is it possible to create a 'shortcut' that would represent each area and could be used as the instructions for totaling each manager area. Something that says - for every computation on this sheet that refers to Bob's area, look at the variable sheet to see which sites are Bob's, then look to the site info on that sheet andcompute. Then on all other sheets, Bob formulas would look to the variable sheet, then back, compute...
I guess it would work off of site names to match or all sites would be on same row numbers.
Thank you very much for all feedback.
Last edited:
