cpatomba18
New Member
- Joined
- Feb 3, 2021
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
I am trying to create a Summary tab that has two drop down validations which pull information from 50 different tabs. Each of the 50 different tabs represents Sales data for each state.
On each state tab, the structure of the information is like this:
Each individual state tab has multiple rows of product specific data laid out similarly to what is shown above (the actual data has upwards of information for 20 different products). There is the exact same number of row spacing between each Product section; will always be two rows between the end of information for one Product and the beginning of another Product. The data also sits in the exact same column; will always be in Column C and D as shown and will never be in let's say Column F. What I would like to do is to bring the information above across 50 different tabs (each tab representing Sales for a different State) onto a Summary tab view that looks like this:
What I have done so far is to try a combination of:
1) INDIRECT - For referring to variable sheet names as each tab has the name of the state abbreviation. This is dynamic based on the State Select Drop Down Data Validation.
2. CONCATENATE or &
3. CELL/ADDRESS function - I created a separate table to try to create a dynamic cell reference lookup that is driven by the Product Select Drop Down Validation Menu. The table that has the cell reference lookup is something like this below:
I wanted to create this table so I could do a lookup to know exactly what is the cell reference I should be pulling.
Ultimately, what I have been trying to do with all of this above is something along these lines:
=INDIRECT("'"&$C$3&"!"&ADDRESS(3,2))
The part I am struggling with is how to make the ADDRESS part dynamic by leveraging the table immediately have that has the Row and Column number based on whether I am trying to pull Product A, Product B, Product C, etc. Can you even embedded that within a VLOOKUP or INDEX MATCH? Tried different ways and have been unable to get it to work.
Any direction or suggestions on how to simplify this is appreciated. Thank You!
On each state tab, the structure of the information is like this:
Each individual state tab has multiple rows of product specific data laid out similarly to what is shown above (the actual data has upwards of information for 20 different products). There is the exact same number of row spacing between each Product section; will always be two rows between the end of information for one Product and the beginning of another Product. The data also sits in the exact same column; will always be in Column C and D as shown and will never be in let's say Column F. What I would like to do is to bring the information above across 50 different tabs (each tab representing Sales for a different State) onto a Summary tab view that looks like this:
What I have done so far is to try a combination of:
1) INDIRECT - For referring to variable sheet names as each tab has the name of the state abbreviation. This is dynamic based on the State Select Drop Down Data Validation.
2. CONCATENATE or &
3. CELL/ADDRESS function - I created a separate table to try to create a dynamic cell reference lookup that is driven by the Product Select Drop Down Validation Menu. The table that has the cell reference lookup is something like this below:
I wanted to create this table so I could do a lookup to know exactly what is the cell reference I should be pulling.
Ultimately, what I have been trying to do with all of this above is something along these lines:
=INDIRECT("'"&$C$3&"!"&ADDRESS(3,2))
The part I am struggling with is how to make the ADDRESS part dynamic by leveraging the table immediately have that has the Row and Column number based on whether I am trying to pull Product A, Product B, Product C, etc. Can you even embedded that within a VLOOKUP or INDEX MATCH? Tried different ways and have been unable to get it to work.
Any direction or suggestions on how to simplify this is appreciated. Thank You!
