Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP - unable to highlight cell range | Excel Worksheet Functions | |||
vlookup function does not work when range is hidden | Excel Discussion (Misc queries) | |||
Vlookup in large named range | Excel Worksheet Functions | |||
Open Workbook - Select Range as table for vlookup | Excel Discussion (Misc queries) | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |