Trying to use cell value as part of file path.

[cameronnicol]

Apologies for the noobiness, I'm a CAD technician who's been roped into helping with some excel stuff. I'm trying to accomplish this using functions as I haven't used VBA before (although I'm open to trying it out).

Problem:

I have a 'master' workbook that contains hundreds of sheets, each with an order number in cell A1. Each order has it's own workbook and the 'master' workbook has a sheet for each order. These sheets contain cells that are linked to the orders own workbook. I would like to be able to update the file path for each linked cell by just changing the value in A1.

At the moment I have something like this in each linked cell:
=C:\Orders\101\[101 - Checklist.xlsx]Sheet1'!$C$10

What I would like is something like this:
=C:\Orders\(A1)\[(A1) - Checklist.xlsx]Sheet1'!$C$10

Where (A1) would be an instruction to copy the order number and paste it into the file path.

I've tried recording a macro to do this, but it always copied the same order number into the file path, instead of copying it from A1. I've also used the find and replace method, but as I'm going to be handing this onto colleagues once completed, I'd rather keep it as simple as possible, so just typing the order number into A1 would be ideal.

I've looked around for solutions but I'm having no luck. Any help would be hugely appreciated.

 

[Scott T]

Try this change event.
Right click on the tab and select view code.
past the code below into the vba editor.
When you copy the sheet the code will also be copied. If the sheet does not have on its sheet module then it will not run.
Change A7 to where you want the link

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then 'check if A1 is the cell that changed
ordnum = Range("A1")
If ordnum = "" Then Exit Sub 'check if a1 is blank if so exit sub
    Range("[COLOR=#ff0000]A7[/COLOR]").Formula = "='C:\orders\" & ordnum & "\[" & ordnum & " - Checklist.xlsx]sheet1'!$C$10" 'puts link to other file in cell
End If
End Sub

 

[cameronnicol]

Try this change event.
Right click on the tab and select view code.
past the code below into the vba editor.
When you copy the sheet the code will also be copied. If the sheet does not have on its sheet module then it will not run.
Change A7 to where you want the link

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then 'check if A1 is the cell that changed
ordnum = Range("A1")
If ordnum = "" Then Exit Sub 'check if a1 is blank if so exit sub
    Range("[COLOR=#ff0000]A7[/COLOR]").Formula = "='C:\orders\" & ordnum & "\[" & ordnum & " - Checklist.xlsx]sheet1'!$C$10" 'puts link to other file in cell
End If
End Sub

Scott, my friend, you are a genius. Thank you for your help! That worked flawlessly and I appreciate your instructions as I've been able to customize it/move things around a bit.

Time to start fool proofing/idiot testing the spreadsheets then, this is when it all goes wrong.

Thanks again, really appreciate the assistance.

 

[Scott T]

You're welcome