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
[vba]
=VLOOKUP($B4,'C:\Documents and Settings\My Documents\[1152_Report WK1152 Final.xls]WK52 weekly'!$B$5:$BL$999,12,0)
[/vba]
eg. for column L, the formulae looks like this
[vba]
=VLOOKUP($B4,'C:\Documents and Settings\My Documents\[1152_Report WK1152 Final.xls]WK52 weekly'!$B$5:$BL$999,13,0)
[/vba]
eg. for column P, the formulae looks like this
[vba]
=VLOOKUP($B4,'C:\Documents and Settings\My Documents\[1152_Report WK1152 Final.xls]WK52 weekly'!$B$5:$BL$999,14,0)
[/vba]
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:
[vba]
=VLOOKUP($B4,'C:\Documents and Settings\My Documents\[A1] & A2'!$B$5:$BL$999,14,0)
[/vba]
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.
Reply With Quote
