keilah
08-23-2007, 04:52 AM
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
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