Forum Discussion

cpatomba's avatar
cpatomba
Copper Contributor
Feb 04, 2021
Solved

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

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 suc...
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Feb 04, 2021

    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

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Feb 04, 2021

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.

  • cpatomba's avatar
    cpatomba
    Copper Contributor
    Feb 06, 2021

    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.

     

     

Resources