Changing range references in Name Manager

%3CLINGO-SUB%20id%3D%22lingo-sub-2057154%22%20slang%3D%22en-US%22%3EChanging%20range%20references%20in%20Name%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2057154%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20getting%20a%20strange%20result%20in%20Excel%20Names%20Manager.%20I%20want%20to%20set%20up%206%20Named%20Ranges%20with%20each%20having%20some%20version%20of%20this%20formula%3A%3C%2FP%3E%3CP%3E%3DOFFSET(Assignments!I3%2C%200%2C0%2CCOUNT(Assignments!H%3AH)%2C1)%3C%2FP%3E%3CP%3Ewhich%20should%20return%20a%20variable%20length%20single%20column%20array%20starting%20at%20I3%20and%20going%20for%20how%20ever%20many%20rows%20that%20have%20numeric%20data%20in%20column%20H.%3C%2FP%3E%3CP%3EWhen%20I%20type%20this%20into%20the%20Refers%20To%20field%20in%20a%20new%20Name%20under%20Name%20Manager%2C%20everything%20works%20great.%3C%2FP%3E%3CP%3EWhen%20I%20go%20and%20type%20in%20the%20second%20entry%2C%20THE%20FIRST%20ENTRY%20CHANGES!!!%20So%2C%20I%20create%20a%20second%20Named%20Range%20with%3C%2FP%3E%3CP%3E%3DOFFSET(Assignments!K3%2C%200%2C0%2CCOUNT(Assignments!J%3AJ)%2C1)%3C%2FP%3E%3CP%3Eand%20it%20works%20fine%20BUT%20THE%20FIRST%20NAMED%20RANGE%20WILL%20HAVE%20BOTH%20%22I%22%20and%20%22H%3AH%22%20changed%2C%20sometimes%20to%20%22K%22%20and%20%22J%3AJ%22%2C%20sometimes%20to%20%22M%22%2C%20%22L%3AL%22%20and%20sometimes%20to%20%22UXV%22%20and%20%22UVU%3AUXU%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAS%20an%20example%2C%20the%20Named%20Range%20shown%20in%20the%20attached%20screenshot%20file%20was%20typed%20in%20as%20%22M%22%20and%20%22L%3AL%22%20but%20have%20been%20changed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20new%20version%20of%20Excel%20but%20don't%20ask%20me%20what%20version%20as%20that%20information%20is%20now%20hidden.%20This%20used%20to%20work%20just%20fine%20on%20Office%202010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20get%20past%20this%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBob%20Martin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2057154%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I am getting a strange result in Excel Names Manager. I want to set up 6 Named Ranges with each having some version of this formula:

=OFFSET(Assignments!I3, 0,0,COUNT(Assignments!H:H),1)

which should return a variable length single column array starting at I3 and going for how ever many rows that have numeric data in column H.

When I type this into the Refers To field in a new Name under Name Manager, everything works great.

When I go and type in the second entry, THE FIRST ENTRY CHANGES!!! So, I create a second Named Range with

=OFFSET(Assignments!K3, 0,0,COUNT(Assignments!J:J),1)

and it works fine BUT THE FIRST NAMED RANGE WILL HAVE BOTH "I" and "H:H" changed, sometimes to "K" and "J:J", sometimes to "M", "L:L" and sometimes to "UXV" and "UVU:UXU".

 

AS an example, the Named Range shown in the attached screenshot file was typed in as "M" and "L:L" but have been changed.

 

This is a new version of Excel but don't ask me what version as that information is now hidden. This used to work just fine on Office 2010.

 

How do I get past this???

 

Thanks in advance,

 

Bob Martin

1 Reply

@Bob_Martin , most probably you need to lock you references, use $ sign for that, like

=OFFSET(Assignments!$I$3, 0,0,COUNT(Assignments!$H:$H),1)