Create Chart Based On Changing Data

Nashkat · Dec 22nd 2006

I am looking for a way to create a [ec]*[/ec] that as the data changes, the chart axis will change, leaving out the blank data. For example, on my spreadsheet, I am using a HLOOKUP formula to find up to 15 lines of data. Not all instances will have the total 15 lines; most are around 8 or 9, but I have to account for the 1 time that all 15 lines will be used. Is there a way to use a formula in the chart to only pull in lines that contain data? Thanks.

venkat1926 · Dec 22nd 2006

Re: Creating A Chart Based On Changing Data

this url will be of help

http://www.tushar-mehta.com/ex…dynamic_charts/index.html

Dave Hawley · Dec 22nd 2006

Re: Creating A Chart Based On Changing Data

Nashkat, there a few ways.

Excel wont plot hidden cells (Tools>Options-Chart) so [af]*[/af] could be used. This method is great when used in conjunction with Custom Views. You can have buttons for macro which will change the chart plotting to any criteria you have saved as a Custom View. [rm]*[/rm] showing any Custom View to get code then Assign the Macro

[advf]*[/advf] could be used to copy only cells housing data.

Excel wont plot #N/A! in most chart types, so you could stick with your current HLOOKUP method.

[dr]*[/dr] are ideal for dynamic charts.

Finally, if you search this forum for dynamic charts you will many examples

Create Chart Based On Changing Data

Participate now!

Share