How do I change the reference in a formula based on a logic test?

C

Carl

I need the formula to change the name of the source file based on a logic
test. For examle, how would I change the following so that it references a
different file name based on the day of the month?
='C:\[MTG Source File 1.xls]Sheet1'!$A$1
I know how to get the day of the month, just haven't been able to get the
name of the file to change in the formula without a reference error. Thanks
so much in advance,
 
B

Bernie Deitrick

With the activecell as the cell with the formula

='C:\[MTG Source File 1.xls]Sheet1'!$A$1

Sub ChangeFileNameTest()
Dim myFormula As String
Dim myFName As String
myFormula = ActiveCell.Formula
myFName = "MTG Source File " & Day(Date) & ".xls"
myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))
MsgBox "Here's the new formula: " & myFormula
ActiveCell.Formula = myFormula
End Sub

Of course, the logic I used for the myFName wasn't based on anything other
than a wild guess....

HTH,
Bernie
MS Excel MVP
 
C

Carl

Thanks, Bernie,
I think you are very close but I got a syntax error on the following steps
(turned red) and could not figure out how to correct it:

myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))

Thanks again, Carl

Bernie Deitrick said:
With the activecell as the cell with the formula

='C:\[MTG Source File 1.xls]Sheet1'!$A$1

Sub ChangeFileNameTest()
Dim myFormula As String
Dim myFName As String
myFormula = ActiveCell.Formula
myFName = "MTG Source File " & Day(Date) & ".xls"
myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))
MsgBox "Here's the new formula: " & myFormula
ActiveCell.Formula = myFormula
End Sub

Of course, the logic I used for the myFName wasn't based on anything other
than a wild guess....

HTH,
Bernie
MS Excel MVP

Carl said:
I need the formula to change the name of the source file based on a logic
test. For examle, how would I change the following so that it references a
different file name based on the day of the month?
='C:\[MTG Source File 1.xls]Sheet1'!$A$1
I know how to get the day of the month, just haven't been able to get the
name of the file to change in the formula without a reference error.
Thanks
so much in advance,
Click to expand...
Click to expand...
 
G

Gord Dibben

Carl

Those two lines are actually all one line.

Enter a line-continuation character and try again..

myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName & _
Mid(myFormula, InStr(1, myFormula, "]"))



Gord Dibben MS Excel MVP

Thanks, Bernie,
I think you are very close but I got a syntax error on the following steps
(turned red) and could not figure out how to correct it:

myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))

Thanks again, Carl

Bernie Deitrick said:
With the activecell as the cell with the formula

='C:\[MTG Source File 1.xls]Sheet1'!$A$1

Sub ChangeFileNameTest()
Dim myFormula As String
Dim myFName As String
myFormula = ActiveCell.Formula
myFName = "MTG Source File " & Day(Date) & ".xls"
myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))
MsgBox "Here's the new formula: " & myFormula
ActiveCell.Formula = myFormula
End Sub

Of course, the logic I used for the myFName wasn't based on anything other
than a wild guess....

HTH,
Bernie
MS Excel MVP

Carl said:
I need the formula to change the name of the source file based on a logic
test. For examle, how would I change the following so that it references a
different file name based on the day of the month?
='C:\[MTG Source File 1.xls]Sheet1'!$A$1
I know how to get the day of the month, just haven't been able to get the
name of the file to change in the formula without a reference error.
Thanks
so much in advance,
Click to expand...
Click to expand...
Click to expand...
 
C

Carl

That took care of the problem. Thanks to both of you.

Gord Dibben said:
Carl

Those two lines are actually all one line.

Enter a line-continuation character and try again..

myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName & _
Mid(myFormula, InStr(1, myFormula, "]"))



Gord Dibben MS Excel MVP

Thanks, Bernie,
I think you are very close but I got a syntax error on the following steps
(turned red) and could not figure out how to correct it:

myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))

Thanks again, Carl

Bernie Deitrick said:
With the activecell as the cell with the formula

='C:\[MTG Source File 1.xls]Sheet1'!$A$1

Sub ChangeFileNameTest()
Dim myFormula As String
Dim myFName As String
myFormula = ActiveCell.Formula
myFName = "MTG Source File " & Day(Date) & ".xls"
myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))
MsgBox "Here's the new formula: " & myFormula
ActiveCell.Formula = myFormula
End Sub

Of course, the logic I used for the myFName wasn't based on anything other
than a wild guess....

HTH,
Bernie
MS Excel MVP

I need the formula to change the name of the source file based on a logic
test. For examle, how would I change the following so that it references a
different file name based on the day of the month?
='C:\[MTG Source File 1.xls]Sheet1'!$A$1
I know how to get the day of the month, just haven't been able to get the
name of the file to change in the formula without a reference error.
Thanks
so much in advance,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use external file variable in formula 2
Excel Formula to add network file link to each month 1
how to refer to the files with regular names? (with formula) 3
how do I link a constant or variable to form 3
IF(AND(Complex Formula in Error in data import before I process it 8
Excel Excel Vlookup formula based on cell data 7
Use cell contents to make formula reference 4
Using =Offset in Charts 1

Top