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.
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