blank cells in offset formula used in chart 
Author Message
 blank cells in offset formula used in chart

Hi,
I have been struggling with this all day.

I have this named formula which I am also using as a data range in a chart:
=offset('dev data'!$E$5,0,0,COUNTA('dev data'!$E:$E), 1)

The problem is that cells 5-12 are blank and there is data from 13 on. I
need to include the blank cells to make my chart accurate.

But the chart only graphs cells 5-13--not the rest of the data as I also
need. I tried changing the last number to something higher but it tells me
"the reference is not valid. it must be a single cell row or column"

any ideas?

thanks

Marci Colwell



Sun, 16 May 2004 04:30:31 GMT  
 blank cells in offset formula used in chart
If the blank cells will not change, you could simply add 8 to the formula:

=offset('dev data'!$E$5,0,0,COUNTA('dev data'!$E:$E)+8, 1)

Quote:

> Hi,
> I have been struggling with this all day.

> I have this named formula which I am also using as a data range in a chart:
> =offset('dev data'!$E$5,0,0,COUNTA('dev data'!$E:$E), 1)

> The problem is that cells 5-12 are blank and there is data from 13 on. I
> need to include the blank cells to make my chart accurate.

> But the chart only graphs cells 5-13--not the rest of the data as I also
> need. I tried changing the last number to something higher but it tells me
> "the reference is not valid. it must be a single cell row or column"

> any ideas?

> thanks

> Marci Colwell



Sun, 16 May 2004 05:09:17 GMT  
 blank cells in offset formula used in chart
Thank you so much!! :)
mpc


Quote:
> If the blank cells will not change, you could simply add 8 to the formula:

> =offset('dev data'!$E$5,0,0,COUNTA('dev data'!$E:$E)+8, 1)


> > Hi,
> > I have been struggling with this all day.

> > I have this named formula which I am also using as a data range in a
chart:
> > =offset('dev data'!$E$5,0,0,COUNTA('dev data'!$E:$E), 1)

> > The problem is that cells 5-12 are blank and there is data from 13 on. I
> > need to include the blank cells to make my chart accurate.

> > But the chart only graphs cells 5-13--not the rest of the data as I also
> > need. I tried changing the last number to something higher but it tells
me
> > "the reference is not valid. it must be a single cell row or column"

> > any ideas?

> > thanks

> > Marci Colwell



Sun, 16 May 2004 06:00:13 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Chart Data to Ignore Blank Cells with Formula

2. Clear the value of a cell that is blank and used by a formula

3. Assigning Blank to a Cell using IF() or other Formula Assignment Op/Func

4. using a formula to fill blank cells

5. OFFSET using cell reference in another cell

6. Create a blank cell using a formula

7. Cell Formula Help - Sum/Offset/Match

8. embedded reference cell in offset formula

9. Cells formula blanks out when I change cells

10. Summing using formulas already in cells, not cell references, or values in cells


 
Powered by phpBB® Forum Software © phpBB Group