Lookup to Variable Sheet Name with Dynamic Cell Address Based on Two Drop Down Validations

cpatomba18

New Member
Joined
Feb 3, 2021
Messages
5
Office Version
  1. 365
Platform
  1. 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:

State Tab.PNG


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:

Summary Tab.PNG


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:

Lookup Sample.PNG


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!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,983
Messages
5,834,708
Members
430,311
Latest member
JAC0617

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top