How to build a path containing a reference to a location variable

[127bit]

Excel 2013 Workbook1 compares same cells of Workbook3 and Workbook2 on local network. In '\\COMPUTER\user\Documents\[Workbook1.xlsx]sheet1'!A2 is the following comparison:

='\\COMPUTER\user\Documents\[Workbook3.xlsx]sheet1'!A2='\\COMPUTER\user\Documents\[Workbook2.xlsx]sheet1'!A2

This formula is repeated in many more cells within Workbook1, relatively referencing its corresponding cells in the other two workbooks. I would like to replace "Workbook3.xlsx" in the path with a reference to a variable stored in Workbook1 I can regularly change. The comparison would then look something like this:

='\\COMPUTER\user\Documents\[REFERENCE1]sheet1'!A2='\\COMPUTER\user\Documents\[Workbook2.xlsx]sheet1'!A2

The REFERENCE1 cell in Workbook1 would contain "Workbook3", but could be changed to "Workbook4", "Workbook5" etc. I haven't been able to correctly combine the literal and relative references needed. Any help here will be appreciated, thanks.

 

[Fluff]

Hi & welcome to MrExcel.
Will the other workbooks be open?

 

[127bit]

Yes, they are open during the compare. Then I copy mismatched values from Workbook3 to Workbook2 until Workbook1 shows no differences. Thanks.

 

[Fluff]

Ok, try it like

=INDIRECT("'\\COMPUTER\user\Documents\["&B1&"]Sheet1'!A2")='\\COMPUTER\user\Documents\[Workbook2.xlsx]sheet1'!A2

where B1 has the name of the workbook including the extension.

 

[127bit]

Ok, try it like

=INDIRECT("'\\COMPUTER\user\Documents\["&B1&"]Sheet1'!A2")='\\COMPUTER\user\Documents\[Workbook2.xlsx]sheet1'!A2

where B1 has the name of the workbook including the extension.

Fluff - you have gotten me very close once I have replaced my (simplified) examples with the actual paths. There is one more reference I hope you can describe: Rather than at [Workbook1.xlsx]sheet1'!B1, that cell's contents are to ideally reside in [Workbook1.xlsx]sheet2'!A2. I'll keep trying combinations but will look for your direction. Thanks.

 

[Fluff]

Which workbook & sheet is the formula on?

 

[127bit]

[Workbook1.xlsx]sheet1. Actually in thousands of cells but assume L2.

 

[Fluff]

In that case try

=INDIRECT("'\\COMPUTER\user\Documents\["&Sheet2!$A$2&"]Sheet1'!A2")='\\COMPUTER\user\Documents\[Workbook2.xlsx]sheet1'!A2

 

[127bit]

Fluff - once I adjusted the literal and relative pointers in 19,800(!) cells this was successful. You also helped me understand the direction justification of the INDIRECT command. I can now carefully begin to parse the rest of the comparison for easier modification.
Thanks a lot, well done.

 

[Fluff]

Glad to help & thanks for the feedback.