Dynamic chart using columns of data based on formulas

[gbonnett]

I need help with a complicated bar chart. I have a table with three columns "Date, Current Schedule, and Our Program". The "Date" column is a number of months, in this case, 360 months, but not all cells may be filled with dates, just formulas waiting for dates in case there is data for those cells. The "current schedule" and "our program" columns are dollar amounts. This chart, for simplicity sake, is an amortization table. One column, "our program" shows it being paid off sooner than the other. I want the bar chart to show how soon the loans will be paid off, but I want the chart to show ONLY the rows that have data, not the blank ones with just formulas. I want the chart to change automatically whenever the data changes (I might have more or less months shown, depending on certain criteria, which change based on other information), and I want the chart to exclude the cells without information in them, the empty cells are always at the end. Get it? I hope I've been clear on this, it's a little confusing to understand, I know. If further explanation is necessary, or maybe a picture, I can try to send that, let me know please. I've been struggling with this problem for months now and would like to get it solved as soon as possible.

 

[Robert Mika]

What you need is 3 named ranges with formula like this:
=OFFSET(Sheet1!$O$2,0,0,SUMPRODUCT(--(LEN(Sheet1!$O$2:$O$14)>0)),1)

where Sheet1!$O$2 is the first cell in your range an the
Sheet1!$O$2:$O$14
is the lenght of the range, you can extend it as much as you need.
To create Named Range go to Name Manger

In my example is a Named Range "Months"

Then you need to go to your chart and change the name of the series to the Named Range

In example above the "Example" is the name of a Workbook and the Months is the series I have changed.
I hope taht helps

 

[gbonnett]

Robert,

This doesn't seem to be working for me, I can't figure out what I'm doing wrong. Did I mention I'm using Excel 2007? Is there a better way to do it with this version?

Thanks for your reply,
Greg