Autoupdating a range with Vlookup?
 

I have a spreadsheet that calculates year to date information across a range
of columns (D through O). The problem is that there is always some info in
all 12 columns. Each month I update formulas to include an additional column
(e.g. sum(D2:J2) to sum(D2:K2). There are quite a few formulas to update. I
use an absolute cell reference in other formulas that specifies the number of
months (e.g. 8 months) and I would like to use a VLOOKUP formula to use this
value to determine what column my SUM formula should use. For example, if it
is month 8 I would like my SUM formulas to change to sum(D2:K2), in month 9
to sum(D2:L2) etc. There must be a simple way to do this - can anyone help?

Thanks.

Autoupdating a range with Vlookup?
 

Maybe you could use a formula like this in M2:

=SUM($D2:OFFSET($M2,0,-1))

I'm not sure how =vlookup() would fit in this.

Erin Dicks wrote:

I have a spreadsheet that calculates year to date information across a range
of columns (D through O). The problem is that there is always some info in
all 12 columns. Each month I update formulas to include an additional column
(e.g. sum(D2:J2) to sum(D2:K2). There are quite a few formulas to update. I
use an absolute cell reference in other formulas that specifies the number of
months (e.g. 8 months) and I would like to use a VLOOKUP formula to use this
value to determine what column my SUM formula should use. For example, if it
is month 8 I would like my SUM formulas to change to sum(D2:K2), in month 9
to sum(D2:L2) etc. There must be a simple way to do this - can anyone help?

Thanks.

--

Dave Peterson

Autoupdating a range with Vlookup?
 

Try this:

=SUM(D2:INDEX(D2:O2,n))

Where n = month number.

n can be a reference to another cell:

A1 = 8

=SUM(D2:INDEX(D2:O2,A1))

Note that if A1 is blank the formula will calculate the entire range.

Or, if the month number is based on the current date:

=SUM(D2:INDEX(D2:O2,MONTH(NOW())))

Biff

"Erin Dicks" wrote in message
...
I have a spreadsheet that calculates year to date information across a
range
of columns (D through O). The problem is that there is always some info in
all 12 columns. Each month I update formulas to include an additional
column
(e.g. sum(D2:J2) to sum(D2:K2). There are quite a few formulas to update.
I
use an absolute cell reference in other formulas that specifies the number
of
months (e.g. 8 months) and I would like to use a VLOOKUP formula to use
this
value to determine what column my SUM formula should use. For example, if
it
is month 8 I would like my SUM formulas to change to sum(D2:K2), in month
9
to sum(D2:L2) etc. There must be a simple way to do this - can anyone
help?

Thanks.