Celly
Board Regular
- Joined
- Jan 29, 2015
- Messages
- 84
- Office Version
- 2016
- Platform
- 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:
www.mrexcel.com
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?
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:
conditional data range for a chart series
Say I got a little table of data like so: A B C 1 Value Flag 2 Jan 5 1 3 Feb 10 1 4 Mar 8 0 5 Apr 6 1 6 May 13 0 7 Jun 15 0 8 Jul 11 1 9 Aug 12 1 10 Sep 4 1 11 Nov 9 0 12 Dec 7 1 Say I want to...
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?
