Creating a Dynamic Chart Combining If/Then Statement and Offset Formula with Named Ranges

tonyainproductdata

New Member
Joined
Aug 3, 2015
Messages
4
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

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

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

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,699
You cannot use a formula other than a simple link formula into a chart.

Try to define another name, something like this:

Name: ProductXMetricYStateZ
RefersTo:
=IF($L$7="Product 1 Metric 1","'WellbeingDashboard.xlsx'!Product1Metric1State1",
IF($L$7="Product 2 Metric 1","'WellbeingDashboard.xlsx'!Product2Metric1State1",
IF($L$7="Product 3 Metric 1","'WellbeingDashboard.xlsx'!Product3Metric1State1",
IF($L$7="Product 4 Metric 1","'WellbeingDashboard.xlsx'!Product4Metric1State1",
IF($L$7="Product 5 Metric 1","'WellbeingDashboard.xlsx'!Product5Metric1State1",
IF($L$7="Product 1 Metric 2","'WellbeingDashboard.xlsx'!Product1Metric2State1",
IF($L$7="Product 2 Metric 2","'WellbeingDashboard.xlsx'!Product2Metric2State1",
IF($L$7="Product 3 Metric 2", "'WellbeingDashboard.xlsx'!Product3Metric2State1",
IF($L$7="Product 4 Metric 2", "'WellbeingDashboard.xlsx'!Product4Metric2State1",
IF($L$7="Product 5 Metric 2", "'WellbeingDashboard.xlsx'!Product5Metric2State1",
ERROR))))))))))

Then use this in the chart:
='WellbeingDashboard.xlsx'!ProductXMetricYStateZ
 

tonyainproductdata

New Member
Joined
Aug 3, 2015
Messages
4
Jon, Thanks for your rapid response! I have attempted to create a named range based on the if/then statement, but I can't seem to get the chart to accept it as an acceptable range for a chart series. The error reads "The formula you typed contains an error. Try one of the following..."

I have checked it for user errors, but can't come up with anything. This is why I tried the indirect formula, thinking that was what I missed.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,699
Just did a quick test.

I created a workbook named DefinedName.xlsx, with names DefinedName.xlsx!aaa through DefinedName.xlsx!jjj, which refer to =Sheet1!$B$19:$L$19 through =Sheet1!$B$28:$L$28. I set up a data validation dropdown in Sheet1!$L$7 with a list of the letters "a" through "j" (to make it easy to test the big messy name), and defined the name "MyData" which refers to:

=IF(Sheet1!$L$7="a",DefinedName.xlsx!aaa,
IF(Sheet1!$L$7="b",DefinedName.xlsx!bbb,
IF(Sheet1!$L$7="c",DefinedName.xlsx!ccc,
IF(Sheet1!$L$7="d",DefinedName.xlsx!ddd,
IF(Sheet1!$L$7="e",DefinedName.xlsx!eee,
IF(Sheet1!$L$7="f",DefinedName.xlsx!fff,
IF(Sheet1!$L$7="g",DefinedName.xlsx!ggg,
IF(Sheet1!$L$7="h",DefinedName.xlsx!hhh,
IF(Sheet1!$L$7="i",DefinedName.xlsx!iii,
IF(Sheet1!$L$7="j",DefinedName.xlsx!jjj,
"error"))))))))))

Then I created a line chart, and manipulated the chart data so its one series had this series formula:
=SERIES(,,DefinedName.xlsx!MyData,1)

When I adjust the dropdown in cell $L$7, the chart shows the data indicated by the name that corresponds to the letter chosen.

Here is the workbook, if you want to play around with it:
DefinedName.xlsx
 

tonyainproductdata

New Member
Joined
Aug 3, 2015
Messages
4
Thanks again, Jon, for your reponse. The challenge I have is that my named ranges have an offset formula attached to them.

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

When I update the named ranges in your sheet to a named range using =OFFSET('Sheet1'!$B$19,0,0,1, COUNTA('Sheet1'!19:19)), the offset formula doesn't work. When I try to create a new chart in your spreadsheet using the offset formula, it doesn't work either.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,699
That should not be a problem, and when I just tested it (changing only a few of the defined names) it was not a problem.

So for example, name "aaa" refers to =OFFSET(Sheet1!$A$19,0,1,1,COUNT(Sheet1!$19:$19))

It should not make a difference, and it does not make a difference.
 

Forum statistics

Threads
1,077,994
Messages
5,337,611
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top