Some weird stuff happening when using a range name in the same sheet. Check out the video.
Never seen this in my over 25 years of using Excel. The file may have issues but definitely some weird stuff.
Found the problem – or more accurately Jan Karel Pieterse Excel MVP did on LinkedIn.
It was as setting in the Options => Advanced => Lotus compatibility settings.
The Transition formula entry option was ticked – it needs to be unticked – see below.
That was a new one on me – big thanks to Jan Karel Pieterse.
It sounds like one of the weird effects of Relative Named ranges.
Go to the Name Manager and check that CurrMth is named as $U$1
If it isn’t, and is say U1 or $U1 that is your explanation. Relative Named Ranges do weird stuff!!
Named ranges are almost always absolute addresses. But that is only by practice not design If when at E1 you name as MyIndex the address $A1, then MyIndex will refer to the cell in the A column but on the same row.
So in cell M15 MyIndex refers to A15, while in cell C3 it refers to A3. This can be useful (if very confusing to most learners).
Similarly you could have Relatively Named ranges such as TwoAbove, or OneRightAndTwoDown by deleting both $ signs.
That is my best guess
Thanks Robert
I had already checked that and it was a fixed reference and it was a workbook scoped name with no sheet scopes for that name.
I also tried creating names on other sheets as well and on the sheet it is defined in, the name behaves as a relative reference but on other sheet it behaves as it should.
Regards Neale
Found the issue – Transition formula entry in Advanced Options. Jan Karel Pieterse found it. Updated the post.