Change all file paths in cells in sheet based on a single cell

[WAshburner]

Hi,

I have a spreadsheet (call it 'Central reporting') that I want to act as a kind of central store for the first sheet (call it 'progress report') of several of workbooks which are spread around various file locations. All the workbooks have an identical layout for the first sheet; however different values in the cells.

What I am trying to do is have a separate tab in the Central workbook for each report which automatically updates whenever anything in the source workbook is changed. It also needs to be easy for someone to add a new report to the Central workbook and link it to a new source file.

What I think might be the 'easiest' way (though I'm happy to be corrected) is to replicate the layout of the progress reports in a tab called 'new report' in the 'Central reporting' workbook, and for each cell that may change value, have a simple formula linking it to the relevant report:

=IF(ISBLANK([filepath])," ",[filepath])

I have used the IF(ISBLANK) formula to stop the annoying 0s when the source cell is blank.

They can then create a copy of the tab and rename it as appropriate.

The difficult bit comes when I or anyone else want to change the file path in the 'new report' tab to add a new report - there are many cells that need changing, so doing each one individually would not be feasible.

Is there a way to let someone paste the new file path to a single cell in the 'new report' tab and have that change the [filepath] in the above formula across all cells in that sheet?

Thanks in advance!

 

[WAshburner]

Solved it!

For those interested:
I input a cell where the file path for each progress report can be copied and pasted in (C3), as well as one to show the current file path being used (C4).
I then used the following macro, run off a button, to automatically change the file path being used in all cells in that sheet.

Sub replace()


Dim Findtext As String
Dim Replacetext As String
Findtext = Range("C4").Value
Replacetext = Range("C3").Value
Cells.replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=False


End Sub

So now, whenever someone wants to add a new report/change the file path, they simply need to copy and paste the destination into cell C3, press the button and hey presto it works!

The current file path being use (C4) gets automatically replaced every time the file path is changed so there is no manual editing involved at all