Create Lookup to Variable Sheet Name and Dynamic Cell Reference Based on 2 Drop Down Validations

[cpatomba]

I have 50 separate tabs of sales data, with each tab representing sales data for a single state.

On each of the 50 tabs, I have sales data for multiple products.  Each product has information such as sales price, discounts, etc.  The structure of each of the 50 state sales tab is like this below in a vertical format (I have upwards of 20 unique products but just listing 2 as an example).  There is spacing between the name of each Product and the product specific amounts such as sales price and discounts that cannot be removed as it contains other product specific data.  However, there is uniformity for each Product listing (i.e. there is always 2 rows that separate the name of the Product and the Sales Price for that product).

Product A

Sales   $50

Discount $25

Net Sales $25

Product B

Sales $55

Discount $25

Net Sales $30

I would like to create a Summary table that has two drop down validations.  The 1st data validation is the State Abbreviation.  The 2nd data validation will be the name of the product (i.e. Product A or Product B as in the example above).  Based on these two data validations, I would like to be able to go to the appropriate state sales data tab and the specific cell reference that has the data.  

So far, I have thought about using some combination of:

1) INDIRECT - Refer to the State Abbreviation drop down validation so I can pull the appropriate state tab as I change the drop down

2) CONCATENATE or &

3) CELL/ADDRESS Function - Use this to refer to the exact cell reference that will have this information on each tab.  For example, if I wanted to find Product A's sale price in each state, it would always be in let's say Cell B4 on every single state tab.

So far, the best I have been able to do is to get to something along these lines:

=INDIRECT("'"&$C$3&"!"&ADDRESS(3,2)), where $C$3 is referring to my State Abbreviation Dropdown.  I don't know how to be able to create a dynamic cell reference lookup in the last part with the address function.  I was thinking of creating a table that has the row and column number that I could lookup to but don't even know where I am going with this.

Any direction would be appreciated.  Thanks!

[#2]

@cpatomba If possible, collect all your sales transactions into one single table. Dedicate one column for the State. Then you can easily summarise the data using pivot tables or filters.

[#3 · Accepted solution]

@cpatomba 

Restructuring your data for analysis is a good option.

Given what you have, traditional Excel is likely to produce an error-prone mess.  Both Excel 365 and Power Query offer sensible ways forward.

For 365, I would identify the data for each State with a sheet scoped defined name.  The task of the INDIRECT formula is then to return data for the State using a fully qualified name.

= LET(
   stateData, INDIRECT(state&"!data"),
   n, XMATCH(Product, stateData),
   IFERROR(INDEX(stateData, n+{2;3;4}), {"Not stocked";"";""})
  )

The picture also shows a product list for a validation dropdown

[#4]

@Riny_van_Eekelen Thanks.  I have tried to restructure the data and right now it is looking like this.  The idea is the same just changed my drop downs to city and division.  Trying to use a combination of index, match and concatenate right now to retrieve the restructured data which is now residing in one single tab inside of 50 tabs.

[#5]

@Peter Bartholomew PowerQuery is a great suggestion here.  Will give it a try also to see what kind of results I may get.