Excel 2007 : Specify Range given a column number

[PMichaud]

I have a spreadsheet that displays a graph of earnings.

I have columns labeled by month, and each row represents a different product. For example, I have a row for Product A, that has revenue numbers for each month, up to the current month. Right under that is the row for Product B. Under those, is a monthly total, which is the sum of product A and product B for that month.

We'll pretend A1 is "January" and L1 is "December".

So, in my graph, I have a data label range that I manually set going from A1:L1 -- that is, every month of the year, ending at the current month, which is December.

Next month, I'll want the graph to go from A1:M1 -- from Jan 2009 to Jan 2010.

I already have a cell which contains the month column as an integer: =MONTH(NOW())+(12*(YEAR(NOW())-2009))

So it's the current month of the year plus 12 times the number of years that the data goes back, which in my case is only 1 -- so right now, that formula returns "12" since it's the 12th month of the first year. Next month it'll return "13."

I already know how to get a total value for the current month. For example, if I want to reference the total for the current month, and the "total" row is row 4, and the month cell is A5, I'd say:

=INDEX(A4:ZZ4,1,A5) [actually, I'd like that to just select all of row 4, but I don't know how]

That gives me the value for the total in the current month.

Now, in the graph I want the range to go from the beginning of time, (ie Jan 2009) to the present month, whatever month that is. I want the present month to automatically be set, I don't want to edit the range to add one more column every month.

For the graph range I tried:

A4:INDEX(A4:ZZ4,1,A5)

But that doesn't work because the INDEX function is returning the value of the cell, not the cell itself. How can I return the cell itself, so I can specify a range that updates dynamically depending on the month?

[Andy Pope]

Search the forum for dynamic named ranges. There are many examples and links to sites explaning how to make your chart series move with the data.

[PMichaud]

Thanks for the lead Andy!

I'm still having a bunch of trouble.

I've tried these:

1) In the Horizontal Axis Labels Range I tried:

=OFFSET(Overview!B$1,1,Charts!$S$3)

Where Overview!B1 is "January," and the column number is Charts!S1 -- that gives me an error about the function being invalid.

2) Next I tried creating a named range with the following formula:

=OFFSET(Overview!B$1,1,Charts!$S$3)

Which didn't give an error, but also didn't select the range in the manager the values read "{...}"

3) So, I thought maybe I just don't understand what the OFFSET returns. I think it returns a range, but maybe it only returns a cell, so I tried:

=Overview!B1:OFFSET(Overview!B$1,1,Charts!$S$3)

But that also gives a range value of "{...}"



Also, for each of those above, I tried specifying 0,0 for the rows and cols, and putting inputting width and height instead. That gave identical results.

I'm stumped...

[Palmetto]

See this link: Dynamic and Interactive Charts.

[deadlyduck]

I'm guessing here - the following formula (or some variation of it) for your dynamic range in the name manager might give you the range of used data:

Please Login or Register  to view this content.