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.

 

[arthurbr]

Perhaps ="C:\Orders"&indirect(a1)&"\["&indirect(A1)&" - Checklist.xlsx]Sheet1'!$C$10"

 

[cameronnicol]

Thanks for the response Arthurbr!

Unfortunately it's giving me a #REF ! error.

I've also just tried =CELL("contents",A1) in place of the file name, as I haven't seen that suggested anywhere. But I'm assuming that function can only be used to populate a cell, not a file path.

 

[arthurbr]

OK I forgot a backslash ="C:\Orders\"&indirect(a1)&"\["&indirect(A1)&" - Checklist.xlsx]Sheet1'!$C$10"

 

[cameronnicol]

Ye I noticed and added it in, still no luck. :/

 

[arthurbr]

With an apostrophe ="'C:\Orders\"&indirect(a1)&"\["&indirect(A1)&" - Checklist.xlsx]Sheet1'!$C$10"

 

[cameronnicol]

With an apostrophe ="'C:\Orders\"&indirect(a1)&"\["&indirect(A1)&" - Checklist.xlsx]Sheet1'!$C$10"

Thanks for the suggestion, i've finished work for the day but i've sent it to myself so i'll give it a go over the weekend.

 

[Scott T]

Try.
Note Indirect only works on open workbooks so XXX-Checklist.xlsx will need to be open or you will get a REF error.

=INDIRECT("'C:\Orders\"&A1&"\["&A1&" - Checklist.xlsx]Sheet1'!$C$10")

 

[cameronnicol]

This method still isn't working, I've messed around with it a bit but no luck. Thanks for the help though

 

[cameronnicol]

Try.
Note Indirect only works on open workbooks so XXX-Checklist.xlsx will need to be open or you will get a REF error.

=INDIRECT("'C:\Orders\"&A1&"\["&A1&" - Checklist.xlsx]Sheet1'!$C$10")

Thanks for the suggestion Scott, the indirect function works perfectly, but it's not ideal in this situation. I'd like it to be able to pull information through without the workbook being open as staff will be dealing with a lot of different orders. Essentially, one department will be updating the 'Order Checklist' workbooks and another department will be using the master workbook.

I'm beginning to think this isn't possible though.