I've been given a work assignment where I need to fill out an individual tab per sales account. Some of the accounts are locations of a larger parent company.
The original template given to me by my organization requires entering data in a single column (C) answering questions/data points described in the adjacent column (B).
I made a summary sheet template for those roll-up accounts. In the summary sheet, I have a column per account (e.g. Column C = Account 1, Column D = Account 2, etc). My first approach was to simply pull in the values from the individual account tabs into a single sheet. For example, the first question I have to input data for in each account tab is "Facility Name." To call the data into the summary sheet I used a formula to call the cell data from each individual account tab.
Working Example:
There are a few issues with this solution. First, it's time consuming to change the formulas. Second, I expect that my leadership group who developed the original template will want to adopt this summary sheet approach as it makes viewing a whole company much easier. The level of Excel expertise is highly variable among the team, but asking people to change formulas, even if they are simple and there are instructions, won't be an effective approach.
I thought that maybe making a list below or to the right of the summary table would be a viable solution.
Hypothetical Solution:
My hope was that I could use a similar formula to my original, but the portion of the formula that calls the sheet name would instead reference a specific cell inputted by the user on the summary table. This would allow other team members to much more simply type in the sheet names for each account, and the data would load into the summary table. That way there isn't the ask to have end users manipulate formulas.
This is what I tried, but I can't figure out a formula to make this work. Right now, I get a #REF error.
First, is this formula incorrect, or am I making another mistake to get the #REF error? If it's incorrect, could someone tell me the proper formula structure (if one exists)? If it's correct, does anyone have any guidance on why I'm getting the #REF error?
Finally, is there a better approach to this overall? The tab names could be changed to anything by any of the other team members so having standard sheet names is unfortunately not possible. The creator of the spreadsheet had a hidden summary tab where he tried to use the INDIRECT function, but it wasn't functional and he just missed deleting the tab because it was hidden. I'm not sure if he was onto something with that or not.
Any expertise anyone can share is greatly appreciated.
The original template given to me by my organization requires entering data in a single column (C) answering questions/data points described in the adjacent column (B).
I made a summary sheet template for those roll-up accounts. In the summary sheet, I have a column per account (e.g. Column C = Account 1, Column D = Account 2, etc). My first approach was to simply pull in the values from the individual account tabs into a single sheet. For example, the first question I have to input data for in each account tab is "Facility Name." To call the data into the summary sheet I used a formula to call the cell data from each individual account tab.
Working Example:
| Question | Account 1 | Account 2 | Account 3 |
|---|---|---|---|
| Facility Name | =Account_1!C11 | =Account_2!C11 | =Account_3!C11 |
| Parent Company | =Account_1!C12 | =Account_2!C12 | =Account_3!C12 |
There are a few issues with this solution. First, it's time consuming to change the formulas. Second, I expect that my leadership group who developed the original template will want to adopt this summary sheet approach as it makes viewing a whole company much easier. The level of Excel expertise is highly variable among the team, but asking people to change formulas, even if they are simple and there are instructions, won't be an effective approach.
I thought that maybe making a list below or to the right of the summary table would be a viable solution.
Hypothetical Solution:
| Account Name | Sheet Name |
|---|---|
| Account 1 | Account_1 |
| Account 2 | Account_2 |
| Account 3 | Account_3 |
My hope was that I could use a similar formula to my original, but the portion of the formula that calls the sheet name would instead reference a specific cell inputted by the user on the summary table. This would allow other team members to much more simply type in the sheet names for each account, and the data would load into the summary table. That way there isn't the ask to have end users manipulate formulas.
This is what I tried, but I can't figure out a formula to make this work. Right now, I get a #REF error.
| Question | Account 1 | Account 2 | Account 3 |
|---|---|---|---|
| Facility Name | ='C84'!C11 | ='C85'C11 | ='C86'C11 |
| Parent Company | ='C84'!C12 | ='C85'C12 | ='C86'C12 |
First, is this formula incorrect, or am I making another mistake to get the #REF error? If it's incorrect, could someone tell me the proper formula structure (if one exists)? If it's correct, does anyone have any guidance on why I'm getting the #REF error?
Finally, is there a better approach to this overall? The tab names could be changed to anything by any of the other team members so having standard sheet names is unfortunately not possible. The creator of the spreadsheet had a hidden summary tab where he tried to use the INDIRECT function, but it wasn't functional and he just missed deleting the tab because it was hidden. I'm not sure if he was onto something with that or not.
Any expertise anyone can share is greatly appreciated.
