Behaviour at what cell a sheet starts

Hi all, I was looking for an explanation to understand Excel's behaviour of the active cell of any given sheet.

E.g.:
- When I switch to any sheet the active cell is wherever I was last time when I left the sheet; that cell name/position is also indiciated in the Name Box
- However, when I start to enter a formula and want to pull an argument from a different sheet - using the keyboard - this seems to work differently:
1. As soon as I start typing an equal-sign the Name Box gets populated with "SUM".
2. If I open the brackets of the formula to enter its arguments and then change the sheet and start moving around with the arrow keys, the starting cell seems to be always A1; unless the sheet has its panes frozen in which case the active cell is the top left corner of the frozen panes (similar to pressing Ctrl+Home with/without panes frozen).

So I'm trying to understand whether this is always the case/why this is or if the behaviour can be changed in any way; e.g. that the starting point for any cell to be used as the formula argument, is the cell where I left the sheet last time.

I hope it's clear what I am trying to get at.

Thanks and Regards

Originally Posted by AliGW

Correct. The = sign indicates that you are typing a formula and SUM is the first in the list of the most common functions used. Excel is trying to be helpful.

I get NPER and I've never used that function

@Raul, when you swich sheets while entering a formula, the scroll position should be as you left it previously, with the name box defaulting to the top left cell. There should be no active cell on the actual sheet workspace, but when you interact by pressing one of the arrows it will move away from the top left cell of the visible scroll area in the direction that you press.

Not that I can see, Ali!

NPER is listed under financial functions, which I rarely use. The last functions that I remember using, countifs, aggregate, index, match and search, so nothing even close.

Adding to the mystery, I just had to restart my laptop, now I have YIELDMAT at the top of the list.

Clicking the dropdown in the name box gives me yieldmat, nper, aggregate, sum, average, if, hyperlink, count, max, sin, more functions...

The 3 in red are functions that I don't rememeber using at any time, possibly once or twice if I've been evaluating a formula that someone else wrote, but that would be all.

I was wondering if yieldmat was a result of me clicking it while lookng through the function lists for nper, but if that was the case then surely the rest of it should be my most recently used functions?

Sorry, Ali!
That was in reply to post #6 (forgot to refresh). I just went back to excel to test your theory from post #7. The category dropdown shows 'most recently used' but the list doesn't match my actual use.

The list in the popup box from clicking the fX icon is the same as the list in the name box.

It would seem more sensible for the list to be based on the formulas that you enter, but I'm wondering if opening a workbook containing formulas affects the list in some way.

I've never used it for functions, only for ranges when moving around large sheets (quicker than scrolling if you know where you need to go).

Even with the list set to most recent, it doesn't appear to change for me. I've just used INDEX 10 or so times, but it still doesn't show up when I start typing again.

Something is definitely a bit off, perhaps MS know it's not working properly but have realised that it's not used much so haven't bothered fixing it.

Think I'm going to wait for the OP to respond to what has been observed so far before looking into it any more.

Now I see it. Don't think that I'll be using it any time soon though.
Changing the dropdown if the fX popup back to most recent moves the selected fuction to the top of the list there. Looks like an overcomplicated way to perform a simple task.