Dynamic Charting - offset forumla?

[Marlene.Sawhney]

Hello Everyone,

I know there are a plethora of dynamic charting posts, but I have a
question that I don't see an answer for. I need the data to update on
a daily basis as new data is added. I have to do this for 50 different
graphs so an easy way to do this would be excellent!

I have the following spreadsheet:

Col(A) Col(B) Col(C) Col(D)
Date Nov 1, 2007 Nov 2, 2007 Nov 3, 2007 .....and so forth
Spread 10 20
30.....................and so forth

As you can see the dates move to the right, across rows. I have used
the following offset/

Nonetheless, I have created name ranges, but i think the offset
formulas is what I'm getting wrong:

For Date I have the following formula: =OFFSET(Sheet1!$A
$1,0,0,COUNTA(Sheet1!$1:$1)-1)
For Spread I have the following forumla: =OFFSET(Sheet1!$A
$2,0,0,COUNTA(Sheet1!$2:$2)-1)

I know this is wrong, but I'm not sure how to change the formula to
reflect the fact that my data is going across columns not rows. Any
help would be great!

 

[Brian Withun]

Hello Everyone,

I know there are a plethora of dynamic charting posts, but I have a
question that I don't see an answer for. I need the data to update on
a daily basis as new data is added. I have to do this for 50 different
graphs so an easy way to do this would be excellent!

I have the following spreadsheet:

Col(A) Col(B) Col(C) Col(D)
Date Nov 1, 2007 Nov 2, 2007 Nov 3, 2007 .....and so forth
Spread 10 20
30.....................and so forth

As you can see the dates move to the right, across rows. I have used
the following offset/

Nonetheless, I have created name ranges, but i think the offset
formulas is what I'm getting wrong:

For Date I have the following formula: =OFFSET(Sheet1!$A
$1,0,0,COUNTA(Sheet1!$1:$1)-1)
For Spread I have the following forumla: =OFFSET(Sheet1!$A
$2,0,0,COUNTA(Sheet1!$2:$2)-1)

I know this is wrong, but I'm not sure how to change the formula to
reflect the fact that my data is going across columns not rows. Any
help would be great!

=OFFSET(Sheet1!$B$1,0,0,1,COUNTA(Sheet1!$1:$1)-1)
This formula will define the range $B$1:$D$1, your three dates.

=OFFSET(Sheet1!$B$2,0,0,1,COUNTA(Sheet1!$2:$2)-1)
This formula will define the range $B$2:$D$2, your three numbers.

It looks like you had omitted the 5th argument to OFFSET(). You were
supplying COUNTA() as the 'height', not the 'width' parameter. I have
added in a height of 1 in my formulas above.

Brian Herbert Withun