Content of cell in Vlookup function

[Ernie Fenwick]

Hi

I have 52 files named "Losses " plus 2 digit week no 01 to 52 plus ".xls"

I have a summary sheet with week numbers in row 2 E to AZ and branch
locations in column C. I want to summarise the losses for each branch for
each week no. I have used a VLOOKUP function which I can manually replicate
for each of the 52 weeks, but it would be nice to pickup the week number
from row 2 and add it to "Losses " and have the formula replcated across the
summary sheet. I have tried looking at INDIRECT and using the ampersand but
I just can't seem to get it right.when I incorporate it into VLOOKUP.

Any help would be appreciated

Ernie

 

[JBeaucaire]

VLOOKUP with INDIRECT cell references is the answer to your question
but it will only work if the sheets are open. You can't INDIRECTL
reference sheets that are closed. You can directly reference them, so i
you need to do that, you'll have to manually create the references

If you're trying to display the information in cell A1 from the shee
Losses12.xls, and the number 12 was in the cell B5 on the current sheet
the INDIRECT formula would be

=INDIRECT(\"[LOSSES\"&B5&\".XLS]SHEET1!A1\"
[/B
IF YOU HAD A SIMPLE VLOOKUP
*=VLOOKUP(_1_, A1:B20,2,FALSE
...and wanted to insert the INDIRECT formula so that the lookup valu
came from the other sheet, just insert it into the underlined field

=VLOOKUP(*_*INDIRECT(\"[Losses\"&B5&\".xls]Sheet1!A1\")*_*
A1:B20,2,false)

 

[Ernie Fenwick]

I have created a simple test sheet with just 12 in B5 and trying to pick up
the value from Losses 12.xls which is currently open. I then get a "The
formula you typed contains an error" dialog which highlights \"[LOSSES\"
as the error. I have tried removing the \ symbols and then get #REF!
error

Regards

Ernie

 

[JBeaucaire]

I don't use spaces in workbook names, sheetnames, range names, anything.
I try not to use them anywhere, it's too much hassle remembering to
account for them. It looks like your workbook has a space in it, make
sure the space is accounted for in the INDIRECT formula.

=INDIRECT("[Losses "&B5&".xls]Sheet1!A1")

Notice this version has the space after the "Losses"