Expert: Bob Umlas - 7/9/2009

Question
QUESTION: Hi,
How can do this?
='[1BW108-1.xls]Review'!$F$55 where 1BW108-1 is on C8.

I have a list of excel files on a folder and a column with some of the excel file names and I want to retrieve the value of F55 for all the files listed on the column.

Thanks,
Alec.

ANSWER: =indirect("'[" & C8 & ".xls]Review'!F55")

but the file must be open or you get a #REF! error

---------- FOLLOW-UP ----------

QUESTION: Nice but I could have like 150 different documents.
Do you know any other way so I don't need to open all?
I could open section by section and copy+past the values of the formula and save so I won't lose the values.
Thanks,
Alec


Answer
Assuming the other values are in C9, C10..., you can do this (follow the steps precisely):
If the ='[1BW108-1.xls]Review'!$F$55 is now in cell A1, put this in cell A1 and fill down to A150:
="='["&c8&".xls]Review'!F55"
(in A2 you should see ="='["&c9&".xls]Review'!F55"
The formula will LOOK right, but will only return the string as shown.
Select A1:A150, copy/paste special values on top of itself.
With A1:A150 selected, use edit/replace, replacing "=" with "=" (no quotes).
You should now have what you want.

HTH