Help on Macro or Formula

[Nigel]

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

 

[Gary Brown]

Try this formula:-

=IF(B2="","",HYPERLINK("S:\Clients\"&B2&"\"&A2&Register!D2&Sheet1!A2&"Quotes
& Orders.xls"))

One word of warning, not really a good idea to uses "&" in filenames
(Quotes and
Orders.xls would be better) unless I have misunderstood?

The above will only create a link to the file, it won't create the
file.

The link above will be created once the client name is entered, you may
want to adapt it so that the Job No, Client name and Job Description all
in place i.e
IF(OR(A2="",B2="",Register!D2=""),"",HYPERLINK("S:\Clients\"&B2&"\"&A2&Register!D2&Sheet1!A2&"Quotes
& Orders.xls"))

Good Luck

Gary

 

[Dave Peterson]

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/[email protected]

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!)