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

[Azza117]

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

 

[Mohamed Shafiee]

Use the hyperlink function

 

[Azza117]

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!

Use the hyperlink function

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

 

[Duke Carey]

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

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!

Use the hyperlink function

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

 

[Bob Phillips]

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)

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

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!

Use the hyperlink function

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