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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top