variable inside a formula?

P

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
 
B

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

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.

Bob said:
In cell A1

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



Phil said:
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
Click to expand...
Click to expand...
 
P

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.

Don said:
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

Phil said:
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
Click to expand...
Click to expand...
 
D

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
 
B

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)

Phil said:
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.

Bob said:
In cell A1

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



Phil said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top