SOLVED Dynamic Range Tables

[vchibisov]

I have:

Week Value
1/1/12 17
1/8/12 12
etc etc

The values get filled in as time passes and go from 0 to positive integer. I want to graph up to the current date.
I made dynamic variables:
Value
=OFFSET($B$2,0,0,weekday(today()),1)
Week
=OFFSET($A$2,0,0,weekday(today()),1)

I then attempted to plug this into a scatter plot:
Y series ='Sheet1'!Value
X series ='Sheet1'!Week

Excel is saying this /\ is wrong.

This is seriously stumping me!

Any help would be appreciated.

Thank you,

Vitaly

[uncle_romania]

Is your dynamic variable attempting to return a particular time period from the overall data? Your variable will just return the 1st 1-7 rows of data depending on what day it happens to be.

Shouldn't it lookup the offset from the todays date minus the 1st date in your data? Like this =OFFSET($B$2,TODAY()-41183,0,1,1) If the 1st date was 1st Oct 2012 and you wanted to return the value for one week ago.

[uncle_romania]

If your doing what I think your doing there is a guide here - http://www.msexcelspreadsheet.com/fu...ta_offset.aspx

[vchibisov]

This is not quite what I am looking for.

The idea is that with each passing week, I have another data point that I want to add to a graph. By the end of the year, the graph will have 52 data points. In the mean time, I do not want a ton of null values cluttering things up. How do I build a graph that will do this automatically? My idea was to make a dynamic variable and then graph it, but that is not working.

[uncle_romania]

I think I know what you mean better now. Does this help http://www.msexcelspreadsheet.com/Ch...lect_data.aspx

[vchibisov]

It seems similar to what I need. The only difference is that I have "0"s instead of null values, but I know how many weeks passed in a year and thus can offset based on today() function. I will play around with it. Thanks.