Link to file using cell value.
Posted by Robert on August 16, 2000 2:15 PM
I am trying to link a cell in workbook1 to a cell in
workbook2 by using a cell above it as the file name.
Example: Workbook1 (named Payroll Totals), cell B4 has
the value 01-11-2000. Workbook1 cell B5 has the formula
to lookup a value in Workbook2 (named 01-11-2000). The
only way I know to do this is to manually open the file
'01-11-2000.xls' and choose the cell I need for the formula.
I am using 'C:\ALL FILES\PAYROLL 2000\[01-11-2000.xls]SHEET1'!$D$5
to get what I need, but want to use the following but can't:
'C:\ALL FILES\PAYROLL 2000\[$B$4.xls]SHEET1'!$D$5.
Is there any way of doing this so I don't have to enter
the date in each VLookup function for 52 payroll files
(since each week is a different file). I don't want to
combine all weeks into one file, as this will be messy.
Again, any help would be greatly appreciated. Thanks.
Posted by Michael Liu on August 16, 0100 2:30 PM
You would have to build the reference in another cell and
then use indirect() to stick it in your vlookup.
Ex:
B4: 01-11-2000 (make sure this is text and not a date)
B5: ="'C:\ALL FILES\PAYROLL 2000\[" & b4 & ".xls]SHEET1'!$D$5"
B6: =INDIRECT(b5)
other stuff you can do:
C5: ="'C:\ALL FILES\PAYROLL 2000\[" & b4 & ".xls]SHEET1'!$A:F"
C6: =VLOOKUP("Total",indirect(c5),5,0)
which will lookup the 5th column entry corresponding to
the row with "Total" in column A of Sheet1 of 01-11-2000.xls
Powerful, but the more external links you create, the slower
it will be when you open the file and tell it to
update links, so beware of abusing this power. =^)
Mike
