Forum Discussion
Solved
Cell reference that will stay relative within a set of folders
I have created a master set of folders for new jobs. At the root of the folder there is a Contractorinfo.xlsx worksheet that includes all of the information for the job. I want the worksheet within the Purchase Order folder to reference cells in the ContractorInfo.xlsx worksheet.
ContractorInfo.xlsx
File within the quotes folder referencing the cell from the ContractorInfo sheet.
I can reference the ContractorInfo cells from the sheet within the Quotes folder and all works great within the Master set of folders. When I copy and paste this set of folders, renaming it for a new project, the spreadsheets still reference the ContractorInfo cells from the Master folder. How can I make the cell reference stay local within the new set of folders and not reference out to the Master set of folders where it was copied from?
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?
