can I create a formula using text made from the concentate comman.
 

As an example, in cell A1 - I have text of someone's name (assume name of
"John")
in cell A2 I have a number (assume number = 1).
In cell B2, I have created a formula that says
="'["&A2&$A$1&".xls]Sheet1!'$D17", which equates to text that says
'[1John.xls]Sheet1!'$D17
I wish to be able to use this formula to create a link to 1John.xls within
an "if" formula, rather than having to manually type in the link for each
different number or name. Is it possible???

Thanks

Use the hyperlink function

"Azza117" wrote in message
...
As an example, in cell A1 - I have text of someone's name (assume name of
"John")
in cell A2 I have a number (assume number = 1).
In cell B2, I have created a formula that says
="'["&A2&$A$1&".xls]Sheet1!'$D17", which equates to text that says
'[1John.xls]Sheet1!'$D17
I wish to be able to use this formula to create a link to 1John.xls within
an "if" formula, rather than having to manually type in the link for each
different number or name. Is it possible???

Thanks

Thanks,

However I am trying to "automate" this process, rather than individually
hyperlinking each link. Basically the links contain information that will be
placed in the master file after a certain date. So I was intending to create
an "if" statement something along the lines of
=if(today=28/03/2005,'[1John.xls]Sheet1!'$D17,""). There will be numerous
people, so I was hoping to try and do this is this sort of way, rather than
setting it up individually for each day / person combination.

Once again, any help is greatly appreciated!



"Mohamed Shafiee" wrote:

Use the hyperlink function

"Azza117" wrote in message
...
As an example, in cell A1 - I have text of someone's name (assume name of
"John")
in cell A2 I have a number (assume number = 1).
In cell B2, I have created a formula that says
="'["&A2&$A$1&".xls]Sheet1!'$D17", which equates to text that says
'[1John.xls]Sheet1!'$D17
I wish to be able to use this formula to create a link to 1John.xls within
an "if" formula, rather than having to manually type in the link for each
different number or name. Is it possible???

Thanks

Use the INDIRECT() function - it will do exactly what you need

"Azza117" wrote:

Thanks,

However I am trying to "automate" this process, rather than individually
hyperlinking each link. Basically the links contain information that will be
placed in the master file after a certain date. So I was intending to create
an "if" statement something along the lines of
=if(today=28/03/2005,'[1John.xls]Sheet1!'$D17,""). There will be numerous
people, so I was hoping to try and do this is this sort of way, rather than
setting it up individually for each day / person combination.

Once again, any help is greatly appreciated!



"Mohamed Shafiee" wrote:

Use the hyperlink function

"Azza117" wrote in message
...
As an example, in cell A1 - I have text of someone's name (assume name of
"John")
in cell A2 I have a number (assume number = 1).
In cell B2, I have created a formula that says
="'["&A2&$A$1&".xls]Sheet1!'$D17", which equates to text that says
'[1John.xls]Sheet1!'$D17
I wish to be able to use this formula to create a link to 1John.xls within
an "if" formula, rather than having to manually type in the link for each
different number or name. Is it possible???

Thanks

Can't use INDIRECT for closed workbooks, and the fact that the OP adds the
workbook name, suggests that he wants to.

VBA solution, such as Harlan Grove's Pull, appears the to be the answer.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Duke Carey" wrote in message
...
Use the INDIRECT() function - it will do exactly what you need

"Azza117" wrote:

Thanks,

However I am trying to "automate" this process, rather than individually
hyperlinking each link. Basically the links contain information that
will be
placed in the master file after a certain date. So I was intending to
create
an "if" statement something along the lines of
=if(today=28/03/2005,'[1John.xls]Sheet1!'$D17,""). There will be
numerous
people, so I was hoping to try and do this is this sort of way, rather
than
setting it up individually for each day / person combination.

Once again, any help is greatly appreciated!



"Mohamed Shafiee" wrote:

Use the hyperlink function

"Azza117" wrote in message
...
As an example, in cell A1 - I have text of someone's name (assume
name of
"John")
in cell A2 I have a number (assume number = 1).
In cell B2, I have created a formula that says
="'["&A2&$A$1&".xls]Sheet1!'$D17", which equates to text that says
'[1John.xls]Sheet1!'$D17
I wish to be able to use this formula to create a link to 1John.xls
within
an "if" formula, rather than having to manually type in the link for
each
different number or name. Is it possible???

Thanks