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.