• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to change the file name in formula automatically

aamirsq

Member
I want to change the file name based on my cell A1.


eg.


Cell A4 ='D:Read Data[13-259.2 Price.xls]WP'!A22


& If cell A1 has any file name then all formula must change to that file name automatically otherwise otherwise remain same.


A1=13-260 Price.xls
 
That's not something I would recommend...links across different workbooks is a recipe for disaster in my opinion. Maybe you could outline a little more about what you are actually trying to achieve.


That said, the quickest way to do this would be by using find and replace - or using a macro that uses this functionality - so that you could replace say 'D:Read Data[13-259.2 Price.xls]WP' with D:Read Data[Some different.xls]WP'


So you would need two cells - one containing the 'old' filepath, and one containing the 'new' filepath. And as soon as the one containing the 'new' filepath changes, a macro does the global find and replace, plus overwrites the cell with the 'old' filepath with the change.
 
This works for me:

[pre]
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [appNewFilepath]) Is Nothing Then
With Application
.EnableEvents = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Cells.Replace What:= _
[appOldFilepath], Replacement:= _
[appNewFilepath], LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

[appOldFilepath] = [appNewFilepath]
With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End If

End Sub
[/pre]
...where:

* appNewFilepath is a named range where the user types the new filepath

* appOldFilepath is a named range containing the old filepath


This code goes in a sheet module for the worksheet where these names are defined.


If you need more help, you'll probably have to upload a sample workbook.
 
Back
Top