change the vlookup via a macro

[keilah]

HI
I have two work books first one called "RevalSummaryYTD0706.xls" Worksheet "RevalTodate and the second workbook called "RevalComparisonModel.xls" worksheet "YTD Reval-y"

I am using a vlookup formula to take a value from column AD and row 8 "first work book"......(=ROUND(VLOOKUP($C8,'[RevalSummary YTD 0706.xls]RevalToDate'!$B$8:$AG$48,31,0)/1000,2) - and returning the value to the second workbook - worksheet "YTD Reval-y" - column K

(=ROUND(VLOOKUP($C8,'[RevalSummary YTD 0706.xls]RevalToDate'!$B$8:$AG$48,32,0)/1000,2) - column L - (same above)

However, each time i add a new column to the right of column AD for the next period "first worksheet", the "31" value stays the same - as you would expect it to i.e. ref to column 31 in the vlookup forumla.....

Here is the question:

Is it possible to amend the forumla via a macro to automatically update (what cell you are referrring in the vlookup - in our case 31) if a column is added to the right of "column AD" change"31" to "32" (see formula) and if it is deleted the do the opposite.........on the click of a macro button.
thanks

[Bob Phillips]

No need, use a formula of

=ROUND(VLOOKUP($C8,'[RevalSummary YTD 0706.xls]RevalToDate'!$B$8:$AG$48,COLUMN('[RevalSummary YTD 0706.xls]RevalToDate'!AG2)-1,0)/1000,2