variable inside a formula?

[Phil]

This is what I'm trying to do.

I have a formula in a cell A2 that reads as follows:
='F:\Job Info\PAY APPLICATION\[blank.xls]Pay App 1'!L13

Is there a way to have the user type in the real name of the file, in say
another cell or a text box(on a form) that will enter itself into the above
formula where blank.xls is?
So when the user types in File1.xls as the name of the file in cell A1, the
formula in cell A2 adjusts to read:
='F:\Job Info\PAY APPLICATION\[File1.xls]Pay App 1'!L13

Can be a marco or click action on a button using VB. Any ideas or a better
way to go about this?
Thanks.

Phil

 

[Bob Phillips]

In cell A1

=INDIRECT("'F:\Job Info\PAY APPLICATION\["&A1&"]Pay App 1'!L13")


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Phil]

Very cool. I didn't know about the INDIRECT function. I opened the excel
help and looked into it more and unfortunately I can't use it because the
info that the formula pulls over is in an external workbook and in order for
the INDIRECT to update the external workbook has to be open, otherwise I get
the #ref error. If I open the other workbook, then it works great. This is
going to be a "master list" of current jobs that reference multiple other
workbooks, so having to open them all for the function to work isn't going
to work. Sorry I didn't let you know the info comes from an external
workbook. But thanks for the response and the new function I learned. I'm
sure I'll use it in the future.

In cell A1

=INDIRECT("'F:\Job Info\PAY APPLICATION\["&A1&"]Pay App 1'!L13")

This is what I'm trying to do.

I have a formula in a cell A2 that reads as follows:
='F:\Job Info\PAY APPLICATION\[blank.xls]Pay App 1'!L13

Is there a way to have the user type in the real name of the file,
in say another cell or a text box(on a form) that will enter itself
into the above formula where blank.xls is?
So when the user types in File1.xls as the name of the file in cell
A1, the formula in cell A2 adjusts to read:
='F:\Job Info\PAY APPLICATION\[File1.xls]Pay App 1'!L13

Can be a marco or click action on a button using VB. Any ideas or a
better way to go about this?
Thanks.

Phil

 

[Phil]

Thanks alot. This looks like it should work fine. I'm new to VB and the Dim
stuff always throws me, but I can follow and understand your example.
Getting ready to head home for the day, so I'll try this later and if I get
stuck I'll post back, but I think I should be able to get it. Thanks.

Phil,
The following macro in a standard module should get you started.

Sub FormulaUpdate()
Dim FileName, Fmla
FileName = Range("A1")
Fmla = "F:\Job Info\PAY APPLICATION\[" & FileName & "]Pay App 1'!L13"
Range("A2") = Fmla
End Sub

Regards,
Don

This is what I'm trying to do.

I have a formula in a cell A2 that reads as follows:
='F:\Job Info\PAY APPLICATION\[blank.xls]Pay App 1'!L13

Is there a way to have the user type in the real name of the file,
in say another cell or a text box(on a form) that will enter itself
into the above formula where blank.xls is?
So when the user types in File1.xls as the name of the file in cell
A1, the formula in cell A2 adjusts to read:
='F:\Job Info\PAY APPLICATION\[File1.xls]Pay App 1'!L13

Can be a marco or click action on a button using VB. Any ideas or a
better way to go about this?
Thanks.

Phil

 

[Don Lloyd]

Phil,
The following macro in a standard module should get you started.

Sub FormulaUpdate()
Dim FileName, Fmla
FileName = Range("A1")
Fmla = "F:\Job Info\PAY APPLICATION\[" & FileName & "]Pay App 1'!L13"
Range("A2") = Fmla
End Sub

Regards,
Don

 

[Bob Phillips]

No problem Phil, just add it to the toolbox<g>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Very cool. I didn't know about the INDIRECT function. I opened the excel
help and looked into it more and unfortunately I can't use it because the
info that the formula pulls over is in an external workbook and in order for
the INDIRECT to update the external workbook has to be open, otherwise I get
the #ref error. If I open the other workbook, then it works great. This is
going to be a "master list" of current jobs that reference multiple other
workbooks, so having to open them all for the function to work isn't going
to work. Sorry I didn't let you know the info comes from an external
workbook. But thanks for the response and the new function I learned. I'm
sure I'll use it in the future.

In cell A1

=INDIRECT("'F:\Job Info\PAY APPLICATION\["&A1&"]Pay App 1'!L13")

This is what I'm trying to do.

I have a formula in a cell A2 that reads as follows:
='F:\Job Info\PAY APPLICATION\[blank.xls]Pay App 1'!L13

Is there a way to have the user type in the real name of the file,
in say another cell or a text box(on a form) that will enter itself
into the above formula where blank.xls is?
So when the user types in File1.xls as the name of the file in cell
A1, the formula in cell A2 adjusts to read:
='F:\Job Info\PAY APPLICATION\[File1.xls]Pay App 1'!L13

Can be a marco or click action on a button using VB. Any ideas or a
better way to go about this?
Thanks.

Phil