PDA

View Full Version : vlookup



benong
01-31-2012, 11:37 PM
hi,
i'm trying to simplify a weekly activity for extracting data from 1 excel file to another excel file.
I'm currnetly using vlookup for this task. I've to modify the formulae in over 50+ columns before applying all the formulae downwards.

eg. for column H, the formulae looks like this

=VLOOKUP($B4,'C:\Documents and Settings\My Documents\[1152_Report WK1152 Final.xls]WK52 weekly'!$B$5:$BL$999,12,0)

eg. for column L, the formulae looks like this

=VLOOKUP($B4,'C:\Documents and Settings\My Documents\[1152_Report WK1152 Final.xls]WK52 weekly'!$B$5:$BL$999,13,0)

eg. for column P, the formulae looks like this

=VLOOKUP($B4,'C:\Documents and Settings\My Documents\[1152_Report WK1152 Final.xls]WK52 weekly'!$B$5:$BL$999,14,0)

and the rest of the columns....

is there a way to simply my task?

Can the formulae reference to a cell value for the excel file name?

eg. cell A1 value contains the excel filename: 1152_Report WK1152 Final.xls
cell A2 value contains the worksheet name: WK52 weekly
And all the formulae will reference to cell A1 & A2 value like this:

=VLOOKUP($B4,'C:\Documents and Settings\My Documents\[A1] & A2'!$B$5:$BL$999,14,0)

In this way, I'll only need to modify 2 cell values and all the formulae will be updated.

Your advise is greatly appreciated, thanks.

wakdafak
02-01-2012, 01:57 AM
i think u should open all the sheet in the same workbook and do the vlookup :content: