named range moves with referring worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-3301920%22%20slang%3D%22en-US%22%3Enamed%20range%20moves%20with%20referring%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301920%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20workbooks%20that%20have%20a%20similar%20structure%20but%20different%20contents%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3EWorkbook%201%3A%3C%2FSTRONG%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E2%20worksheets%3A%201.%20results%2C%202.%20business%3C%2FLI%3E%3CLI%3E%22business%22%20has%20a%20workbook%20scoped%20named%20range%20that%20refers%20to%20row%20368%3A368%20called%20%22profit%22%3C%2FLI%3E%3CLI%3E%22results%22%20has%20a%20few%20cells%20that%20refer%20to%20the%20%22profit%22%20named%20range%20in%20an%20index%2Fmatch%20formula%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3EWorkbook%202%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3E1%20worksheet%20called%20%22business%22%26nbsp%3B%3C%2FLI%3E%3CLI%3E%22business%22%20has%20a%20workbook%20scoped%20named%20range%20that%20refers%20to%20row%20759%3A759%20called%20%22profit%22%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20I%20am%20seeing%20occasionally%20is%20when%20I%20move%20the%20%22results%22%20worksheet%20to%20workbook%202%20and%20click%20yes%20to%20the%20name%20conflicts%2C%20the%20row%20number%20that%20the%20%22profit%22%20named%20range%20is%20scoped%20to%20changes%20to%20row%20368%20in%20workbook%202.%20That%20is%20the%20scope%20for%20%22profit%22%20in%20workbook%201%2C%20why%20is%20it%20changing%20in%20workbook%202%3F%26nbsp%3B%20Unfortunately%20this%20only%20happens%20occasionally%20when%20repeating%20the%20same%20steps.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3301920%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Visitor

I have two workbooks that have a similar structure but different contents:

Workbook 1: 

  • 2 worksheets: 1. results, 2. business
  • "business" has a workbook scoped named range that refers to row 368:368 called "profit"
  • "results" has a few cells that refer to the "profit" named range in an index/match formula

Workbook 2:

  • 1 worksheet called "business" 
  • "business" has a workbook scoped named range that refers to row 759:759 called "profit"

 

The issue I am seeing occasionally is when I move the "results" worksheet to workbook 2 and click yes to the name conflicts, the row number that the "profit" named range is scoped to changes to row 368 in workbook 2. That is the scope for "profit" in workbook 1, why is it changing in workbook 2?  Unfortunately this only happens occasionally when repeating the same steps. 

 

0 Replies