keyboard shortcut to navigate back to cell where a formula is being entered

[happymug]

Hello,

Does anyone know of a keyboard shortcut to jump back the cell where a formula is being entered after selecting cell references located on a different sheet?

Thanks so much for any suggestions!

 

[offthelip]

welcome to the board
If you hit the ENTER key it will go back to the cell you are entering the formula , if you then want to carry on entering other references just hit the F2 key ( function key 2)

 

[happymug]

Thanks for the suggestion! If the formula is incomplete, hitting enter causes excel to issue an error message.

I'm looking for a specific keyboard shortcut. For example, let's say the goal is to sum a 1000 row column of numbers and place the sum result at the top of the column. Typing "=sum(" then using the arrow keys to highlight the first cell, then pressing ctrl-shift down-arrow selects the entire column moves the view such that the cell with the formula in is off the screen and not visible, but then typing ")" and pressing enter brings the view back to the cell with the sum formula in it.

Now let's say an if-function is used with a 1000 row column as an input. After selecting the column via ctrl-shift down-arrow, then typing a "," the view does not go back to the cell with the if-function in it. Pressing ctrl-backspace brings the view back to the cell with the formula in it. Ctrl-backspace brings the view back to the cell with the formula as long as the formula variable are located within the same sheet.

Ctrl-backspace doesn't bring the view back to the cell with the formula in it when writing a formula on one sheet that requires cell-references from other sheets. In other words, starting the formula in a cell, navigating to another sheet and selecting a cell, ctrl-backspace doesn't bring the view back to the cell with the formula in it.

I'm looking for a keyboard shortcut that brings the view back to the cell with the formula after entering a cell-reference on a different sheet. Hope that makes sense.

 

[Alex Blakenburg]

I'm looking for a keyboard shortcut that brings the view back to the cell with the formula after entering a cell-reference on a different sheet.

Try this.
After entering a cell-reference on a different sheet.
Enter a comma to indicate you want to move on to the next parameter.
Then hit F4. It will emit a sound indicating it considers it an error BUT it does take you back to the cell with the formula.

If you are not too many sheets away, Ctrl+PgUp or PgDown back to the original sheet works too.

 

[Alex Blakenburg]

Once you refer to another sheet in a formula it uses that sheet references for all cell references including those on the current sheet.
This clutters up the formula making it harder to read.
So I tend to try to select all the references on the "other sheet" in one go putting place marker cell references for the current sheet. (the real references if I can remember them which is generally not the case).
This saves you from having to go back to the formula each time and makes it easier to avoid having the clutter of having current sheet references in the formula

eg A1 & B2 are place holder or best guess.
Hit enter to finish the formula and when back on sheet 1 replace them with the real cell references.
If you then select them and use the mouse to repoint them to another cell it does not add the sheet reference.