Jan 13 2021 08:03 AM
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
Jan 13 2021 08:10 AM
@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)