Forum Discussion
Cell reference that will stay relative within a set of folders
Ah - the LET function is only available in Excel 365 and 2021.
Please change the 'Refers to' of the defined name ContractorInfo to
=TRIM(LEFT(SUBSTITUTE(CELL("filename",Sheet1!A8),"\",REPT(" ",255),LEN(CELL("filename",Sheet1!A8))-LEN(SUBSTITUTE(CELL("filename",Sheet1!A8),"\",""))-1),255))&"\[ContractorInfo.xlsx]"
(You can do so in Formulas > Name Manager)
Open a workbook in the Purchase Orders folder.
On the Formulas tab of the ribbon, click Define Name.
Enter ContractorInfo in the Name box, and enter the following formula in the Refers to box:
=LET(path,CELL("filename",'Sheet1'!A8),depth,LEN(path)-LEN(SUBSTITUTE(path,"\","")),newpath,SUBSTITUTE(path,"\",REPT(" ",255),depth-1),parent,TRIM(LEFT(newpath,255)),parent&"\[ContractorInfo.xlsx]")
where Sheet1 is the name of a worksheet in the workbook.
You can now use a formula such as
=INDIRECT("'"&ContractorInfo&"Project Information'!B2")
Thank you for your response! When I enter the =INDIRECT formula a window pops up as follows:
Update Values: Sheet1 (looking for a file name)
Did you replace Sheet1 with the name of a sheet in your workbook?
- I changed the name of the sheet in my workbook to Sheet1 and copied the formula.
Just to make sure: Sheet1 is the name of a sheet (any sheet) in the workbook with the formula (the workbook stored in the Purchase Order subfolder). It is used in the definition of ContractorInfo.
The cell formula uses Project Information, which is a sheet in the ContractorInfo.xlsx workbook.
And I forgot to mention that ContractorInfo.xlsx should be open in Excel too.