Aug 25 2022 08:17 AM - edited Aug 25 2022 08:35 AM
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?
Aug 25 2022 08:46 AM
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")
Aug 25 2022 05:29 PM - edited Aug 25 2022 05:31 PM
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)
Aug 26 2022 12:10 AM
Did you replace Sheet1 with the name of a sheet in your workbook?
Aug 26 2022 06:05 AM
Aug 26 2022 06:24 AM
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.
Aug 26 2022 02:12 PM
As soon as I paste the formula this box pops up.
When I use the dialog that comes up and navigate to the file the #NAME? error is displayed.
Aug 26 2022 02:16 PM
The sheet in ContractorInfo.xlsx should be named Project Information, as mentioned in your first post (and in my previous reply).
Aug 26 2022 02:48 PM
Thanks so much for your help! I have all of the sheets named as you mentioned. Here is the error.
Aug 26 2022 02:53 PM
Scripts? Which version of Excel / Office do you have?
Aug 26 2022 03:00 PM
Aug 26 2022 03:15 PM
SolutionAh - 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)
Aug 26 2022 03:24 PM
Aug 26 2022 03:15 PM
SolutionAh - 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)