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.
='\\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.