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.
>
>