It looks like you want to build that path and filename and use that in the
formula.

I think you'd want to use =indirect() to return your values, but the bad news is
that =indirect() won't work with closed workbooks.

Harlan Grove wrote a UDF called that will retrieve the value from a closed
workbook.

If the reference doesn't exist, it shows an error, but you could use:

=if(iserror(pull(...)),"",pull(...))

You can find Harlan's Pull UDF at:
http://groups.google.co.uk/groups?se...wsranger.co m

But sometimes Google will create errors when copying|pasting. You can find the
same function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/

You can find his pull.zip there (and shouldn't have any trouble with google!)

Nigel wrote:

Hi,
i hope someone can help me. i need to create a formula that sits in a cell
and looks for data. ( obvioiusly ). however, the formula needs to be in place
even though the file from ehere the data comes from might not be there yet. (
i have to create a book that when a new file is created, the links are
already in place ). i think it could work with an IF type formula for ( if
B2="",""). here is my information.

Cell description:
A2 = Job no.
B2 = Client Name
D2 = Actual Spend on project
Register!D2 = Job Description
Register!H2 = Quoted Amount

my path is S:\Clients\ & Client Name & \ & Job Number & Job Description &
Job Number & " Quotes & Orders.xls
if H2 cell has no value then active cell shows no value.
( this cell will indicate the file created and filled in )

Can a formula be written to take the above details and create a path to a
proposed file?

Thanks in anticipation,

Nigel

--

Dave Peterson