Options for filtering a chart series

Celly

Board Regular
Joined
Jan 29, 2015
Messages
81
Office Version
  1. 2016
Platform
  1. Windows
Greetings!

Say I have a sheet with data in range A001:AXXX. The XXX can vary in size based on another cell value (MAX_YEARS). This is a financial model. Odd rows have the data in nominal dollars, and even rows have the data in real dollars. I am looking for the cleanest solution to allow charting of only the nominal data or only the real data. Here are the solutions I am currently aware of:

Solution 1: Employ an auto filter on a dynamic range and use that range as the chart series. This works relatively well, however it visually filters the data on the sheet and couples the chart filtering to that visual filtering. I would like to decouple this so that potentially the data is visually filtered one way on the sheet, while the chart another.

Solution 2: I found this thread:


This potentially can work, however it employs INDIRECT ranges and is a bit brutal for a complex project with a lot of charting.

Solution 3: I can dynamically built a non-contiguous range through VBA, i.e. { A1,A3,A5,A7.. } This sort of works, but these non-contiguous ranges are unwieldy and I lose the ability to use OFFSET on them to get a fully dynamic size based on that MAX_YEARS cell value.

Solution 4: Excel 365 has a new FILTER function which seems to address this difficulty, however I need to maintain backwards compatibility to Excel 2007.

Solution 5: Refactor the nominal/real data into columns instead of rows. This would solve all my charting difficulties, but the row based approach has other significant advantages and this is not a good solution.

Is there a sixth solution I should be evaluating?
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

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
Top