Update formula Col reference Macro

[xcelnovice]

Is there a way to use a macro to update a column a sum if or vlookup formula is pulling data from. Ex for a vlookup I have 12 mos of budget data in columns. In Feb I am pulling data from the 8th Col in the table. I'd like to run a macro that would update that number by 1 to pull Col 9 next month and Col 10 the following. It'd be similar with the sum of but increase from Col D to Col F etc. Thanks

 

[dave3009]

Hi xcelnovice,

Are you sure you really need to do this with VBA? Here are some dynamic examples that work using worksheet functions to pick column number.

Excel 2007
	A	B	C	D	E	F
1		Jan	Feb	Mar	Apr	May
2	Data 1	14181	16800	15578	19393	12412
3	Data 2	15203	15498	14216	17863	13151
4	Data 3	12479	17368	10104	12234	19279
5	Data 4	19157	17592	15380	12116	16113
6
7		Feb	Feb
8	Data 1	16800	16800
9	Data 2	15498	15498
10	Data 3	17368	17368
11	Data 4	17592	17592
Sheet3

Cell Formulas
Range	Formula
C7	=B7
C8	=VLOOKUP(A8, $A$2:$F$5, MONTH($C$7)+1, 0)
B8	=INDEX($A$1:$F$5, MATCH($A8, $A$1:$A$5, 0), MATCH(B$7, $A$1:$F$1, 0))

 

[xcelnovice]

Awesom the index match worked great. Is there a more dynamic sum if? Thanks for the help!

 

[dave3009]

Using the same values and ranges here are two options

Excel 2007
	D
7	Mar
8	55278
9	55278
Sheet3

Cell Formulas
Range	Formula
D7	=C7
D8	=SUM(OFFSET(A1, 1, MATCH(D7, $B$1:$G$1, 0), 4, 1))
D9	=SUM(INDEX($A$1:$F$5, 2, MATCH(D7, $A$1:$G$1, 0)):INDEX($A$1:$F$5, 5, MATCH(D7, $A$1:$G$1, 0)))