Excel - Create chart from range of cells while excluding null values?

Question

I've got this Excel sheet which basically contains a lot of data. Now, this Excel sheet is updated dynamically via a macro that imports the data. So the data might change, meaning, some cells might be populated, while others won't.

So I've got this formula in Sheet 2 in each cell from A2:A60 to M2:M60 which basically looks like this:

=IF(Sheet1!E2<>0;Sheet1!A2;"")

Meaning, if cell E2 on the row I'm in is 0, then the copied value in the new spreadsheet is nothing. Same goes for the next row:

=IF(Sheet1!E3<>0;Sheet1!A3;"")

This is repeated until row 60.

Now, what I want to do is to select the range A2:A60 and insert that data to a chart. But the thing is, the chart adds the cells that doesn't have a value. I want to exclude it from the chart without having to change the range of the chart. Is this possible using a formula in the Chart selection? Or would I have to use a macro?

Edit: Right now, the chart looks like this when I create it based on A2:A60. Notice that only A4:A17 actually have any value, the other ones have nothing, because of the formula described above.

Answer 1

You can probably use named range.

You can define names like :

Name    Definition
Date    =OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1)
Value   =OFFSET(Sheet1!$B$1,1,0,COUNTA($B:$B)-1)

And then use them as a source in your chart.

You can find more information on MS Website.

[EDIT] Here are two other examples:

• a great tutorial: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
• a shorter one but with a sample example (in case you couldn't read the one i've posted in my comment): http://www.databison.com/index.php/create-chart-using-named-range-in-excel/

[EDIT 2] Results of the work within the chat:

Problem: the COUNTA formula did not work on the worksheet because the cells contained formulas so the COUNTA still counted these cells even if the value was empty.

Solution: we used the SUMPRODUCT formula to count the empty values

Name   Definition
Date   =OFFSET(Sheet1!$A$2,1,0,SUMPRODUCT(IF(Sheet1!A2:A60<>"",1,0)),1))
Value  =OFFSET(Sheet1!$B$2,1,0,SUMPRODUCT(IF(Sheet1!B2:B60<>"",1,0)),1))

Regards,

Max