Biff suggested I check out responses on this as there may be a VBA routine
that does this. I want to take the formula below and in place of the file
name have a variable value that looks up the data on another workbook while
it is closed.
example:
A1 = June.xls
B1 = =IF(ISBLANK('X:\Y\[A1]SomeSheet'!A1),"",'X:\Y\[A1]SomeSheet'!A1)

the value in A1 can potentially change depending on other factors within the
active sheet. But the value in B1 is always based off of the file name in
A1. I would prefer to do this in a formula or array if at all possible
instead of setting a VBA routine to enter this formula everytime the page is
opened.


"Harlan Grove" wrote:

John Whitehead via OfficeKB.com wrote...
I would like the Excel spreadsheet to do this. The person I'm trying
to
help needs to take classes on Excel.

You could pull in data with formulas like

=IF(ISBLANK('X:\Y\[Z.xls]SomeSheet'!A1),"",'X:\Y\[Z.xls]SomeSheet'!A1)

Pulling data by formula or copy/paste are your only alternatives.