The function you want to use is =indirect().
The bad news is that =indirect() will return an error if that other workbook
isn't open.
Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.
You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip
GoBobbyGo wrote:
Is there a way to refer to use a variable as the sheet or file name in an
excel formula?
For example, let's say that in columns A and B, I've got the names of a set
of workbooks and sheets that I want to refer to...
Column A Column B
Book1 Sheet1
Book1 Sheet2
Book1 Sheet3
Book2 Sheet1
Book2 Sheet2
Book2 Sheet3
Book3 Sheet1
Book3 Sheet2
Book3 Sheet3
And let's say I want to know what's in cell A1 in each of those sheets...
So in cell C1, I've got
=[Book1.xls]Sheet1!$A$1
Is there anything I can do short of writing a macro (which is what I'm doing
now) to just get Excel to copy a similar formula into all the rows, where
it's interpreted as
=[{workbook name in column A}]{Sheet name in column B}!$A$1
--
Dave Peterson