repost: plz help- dynamic range with gaps?

[KR]

Excel 2003 on Win 2000

I have a range of cells that I need to graph; each cell in the range has a
formula, but may or may not have actual data (so I can use an IF statement
to leave it blank or N/A).

My (internal) customer wants a column graph that only shows X-axis points
for the cells that have values- e.g. I need to supress the blank values
completely. How can I do this? I'm thinking a named range, but I don't know
what formula will give me the desired results...


For example:
(raw data)
Cats 100
Dogs 150
Ferrets 0 (or NA)
Fish 50

my graph needs to show:

X
X X
X X X
Cats Dogs Fish

Help!
Thanks,
Keith

 

[Domenic]

Assuming that A2:B5 contains your data, and your formula for Column B
returns 0 when your IF statement is FALSE, try the following...

D2, copied down and over to the next column, Column E:

=IF(ROWS($D$22)<=COUNTIF($B$2:$B$5,">0"),INDEX(A$2:A$5,SMALL(IF($B$2:$B
$5>0,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($D$22))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Then, define the following dynamic ranges...

Insert > Name > Define

Name: ChartLabels

Refers to:
=Sheet1!$D$2:INDEX(Sheet1!$D$2:$D$65536,MATCH(2,1/(Sheet1!$D$2:$D$65536<>
"")))

Click Add

Name: ChartValues

Refers to:
=Sheet1!$E$2:INDEX(Sheet1!$E$2:$E$65536,MATCH(2,1/(Sheet1!$D$2:$D$65536<>
"")))

Click Ok

Lastly, change the chart's 'Data Source'...

1) Click on the column series to select it

2) In the 'Formula Bar', change the cell references to the named
ranges...

=SERIES(Sheet1!$E$1,Sheet1!ChartLabels,Sheet1!ChartValues,1)

(Sheet1!$E$1 contains the header/label for Column E.)

Hope this helps!

 

[KR]

Wow, I'll both test this, and dissect it until I understand how it works!
Thanks a bunch,
Keith