SOLVED

Cell reference that will stay relative within a set of folders

Occasional Contributor

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.

Dana220106_0-1661437238149.png

ContractorInfo.xlsx 

Dana220106_2-1661440104613.png

File within the quotes folder referencing the cell from the ContractorInfo sheet.

Dana220106_1-1661437369402.png

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?

 

12 Replies

@Dana220106 

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")

@Hans Vogelaar 

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)

 

 

@Dana220106 

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.

@Dana220106 

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.

 

@Hans Vogelaar 

As soon as I paste the formula this box pops up.

Dana220106_0-1661548100905.png

Dana220106_1-1661548209450.png

When I use the dialog that comes up and navigate to the file the #NAME? error is displayed.

 

 

@Dana220106 

The sheet in ContractorInfo.xlsx should be named Project Information, as mentioned in your first post (and in my previous reply).

@Hans Vogelaar 

Thanks so much for your help! I have all of the sheets named as you mentioned. Here is the error.

Dana220106_0-1661550419877.png

 

@Dana220106 

Scripts? Which version of Excel / Office do you have?

Microsoft Office Pro Plus 2016
best response confirmed by Dana220106 (Occasional Contributor)
Solution

@Dana220106 

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)

Thanks so much for your help... and patience! It worked!!!!