Dynamic chart using named ranges and indirect function

Question

I have two worksheets in a workbook (WellbeingDashboard); Charts and Data.  The data I am looking at includes product data (five products), accounts and user metrics (two metrics), by state (two states).  I am trying to create a single chart (stacked area) that trends the change in a user-selected metric for a user-selected product over a period of time.

The Data page includes several lines of data with named ranges.  All named ranges use the following formula so that the chart automatically updates as new data is entered in each month:

Named Range Example on Data Page: Product1Metric1State1 =OFFSET('Data'!$C$4,0,0,1,(COUNTA('Data'!$4:$4)))

I am using these named ranges on the Charts Page to create a dashboard.

The user can select one of five products from a data validation in-cell dropdown in cell F2:  Product 1, Product 2, Product 3, Product 4, Product 5

The user can select one of two metrics from a separate data validation in-cell dropdown in cell F4:  Metric 1, Metric 2

These dropdowns combine in cell L7 for a single lookup value:  =CONCATENATE('Charts'!$F$2, " ", 'Charts'!$F$4)

This lookup value is then used in cell M10 to return the named range value using a compound if/then statement for the first state's named range (one state per data series):

=IF(L7="Product 1 Metric 1","'WellbeingDashboard.xlsx'!Product1Metric1State1",

IF(L7="Product 2 Metric 1","'WellbeingDashboard.xlsx'!Product2Metric1State1",

IF(L7="Product 3 Metric 1","'WellbeingDashboard.xlsx'!Product3Metric1State1",

IF(L7="Product 4 Metric 1","'WellbeingDashboard.xlsx'!Product4Metric1State1",

IF(L7="Product 5 Metric 1","'WellbeingDashboard.xlsx'!Product5Metric1State1",

IF(L7="Product 1 Metric 2","'WellbeingDashboard.xlsx'!Product1Metric2State1",

IF(L7="Product 2 Metric 2","'WellbeingDashboard.xlsx'!Product2Metric2State1",

IF(L7="Product 3 Metric 2", "'WellbeingDashboard.xlsx'!Product3Metric2State1",

IF(L7="Product 4 Metric 2", "'WellbeingDashboard.xlsx'!Product4Metric2State1",

IF(L7="Product 5 Metric 2", "'WellbeingDashboard.xlsx'!Product5Metric2State1",

ERROR))))))))))

This is where I am getting stuck.  I am then trying to leverage the contents of cell M10 for the data series.  I have tried a number of various formulas for the data series, but have had no success.  I believe that my error is either in the step above (what the "then" statement is") or the formula itself for the data series.  Here is what I have tried for the data series.

Works:

• ='WellbeingDashboard.xlsx'!Product1Metric1State1  (Tried all individual variations to ensure named ranges work)

Doesn't Work

• =IF(L7="Product 1 Metric 1", 'WellbeingDashboard.xlsx'!Product1Metric1State1, “”)
• =IF(L7="Product 1 Metric 1", Offset(Indirect(‘WellbeingDashboard.xlsx’!$B$4,0,0,1,CountA(‘Data'!$4:$4)),0))
• =Indirect(IF(L7="Product 1 Metric 1", 'WellbeingDashboard.xlsx'!Product1Metric1, “”))
• =Offset(‘WellbeingDashboard!$B$1,0,0,1,CountA(‘Data'!$4:$4))
• =Offset(Indirect(‘Data'!$B$4,0,0,1,CountA(‘Data'!$4:$4)))
• =Indirect(M7)
• =M7

I am at a loss, so I would appreciate any and all ideas you might have!  Thanks!

Answer 1

Can you upload your xlsx file on OneDrive ?

You may replace your confidential data with some logical dummy data (if necessary).