I am coming across the damndest thing. Say I have two tabs. To make
things simple lets says they are "Sheet1" and Sheet2" the default
startup tabs.

I will make a list from 1 to 100 in A1:A100 in "Sheet1"

In Sheet 2, I type in "=Sum(" in B2 and then switch over to "Sheet1"
and highlight A3:A14. Before pressing enter, the cell says
"=SUM(Sheet1!A3:A14"

Upon pressing enter, I get no value in "Sheet2" and in cell B2 in
"Sheet1" I get "=SUM(Sheet1!#REF!)".

If I go ahead and switch back to "Sheet2" before pressing enter, the
value will still be in B2 of "Sheet1" and will be "=SUM(Sheet1!B4:B15)"

I have never seen this kind of behavior in my 6 years of working with
Excel.

The only way I have found that a reference will work is if I directly
type in the entire formula without changing sheets.

If anyone has any idea whats going on, please let me know