dynamic horizontal range with CHART

[daveyc18]

ok, so i tried the offset formula and it's fine, but it breaks down because when I add data, it's NOT to the right , im inserting a new column of data in column B...this causes the offset starting point (ie B$2$) to shift when I insert a new formula...therefore, the chart is NOt dynamic as it doesn't capture today's data (it'll capture from column C now, which is yesyerdays data, to the last column to the right)

how do I fix this??? and it's so annoying how you can't easily use named ranged for series chart data? it appears you need to have the workbook name BEFORE the named range? i manually typed in the workbook name , but is there a faster way to do this?

 

[Fluff]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply links to all other sites where you have asked this question
Thanks

 

[nardagus]

Well in Offset formula you could use INDIRECT. I.e.

=Offset(Indirect("$B$2"),0,0,49,1)

INDIRECT converts string address to actual cell address. This way adding a new column won't move offset starting point.

About named range. Correct me if I'm wrong but when you create a named range on workbook level you have to use workbook name. When you create named range on worksheet lvl you can skip workbook name. At least that how it looks in my excel I made many years ago (I usually don't use charts and named ranges)

 

[Jon Peltier]

INDIRECT is not usually a great option.

If you're inserting data in column B, use a cell in column A as your OFFSET anchor.

So change (for example)

=OFFSET(B2,0,0,12,1)

to

=OFFSET(A2,0,1,12,1)