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?