OK...I can't attach a sheet but this seems to be a rather easy problem so perhaps my explanation will suffice.

My wife has a kludgy sheet with data from A16:AH80. In Z86 (i.e. below this table) there is a formula of mammoth kludge proportions. For the heck of it, here it it...



=+N44+P30+Q44+S30+S33+T33+U34+U33+V33+W33+X17+Y21+ Y22+Y23+Z39+Y40+W38+Y42+Y43+X46+Y46+Z46+Z51+Y51+X5 1+Y52+X52+V55+Y55+W58+Y60+X63+Y64+W65+Y66+Y67+Y68+ X70+Y73+Y74+Y75+Z72+1500

The problem is, if she sorts her table, the cell references stay the same and thus the formula returns a different result based on the new values in those cells.

I've never had formulas like that so I've never encountered this problem but it seems that cell references in formulas do not update with a sort. Is that truly the case?

Yup, that's how it works...
If you need to sort on such results, try copying & using paste special to turn them into values, then sorting on the column with the values rather than the one with the formulae.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Just for info here is another way to Paste values only ( on the same sheet) than using the right click menu

Long explanation, rapidly done

Select the range containing the formulas of which you want to keep the values

Put your cursor against the upper boundary of the selected zone -A cross appears -

Press right button of your mouse and WHILE KEEPING THE BUTTON PRESSED move the selected range to where you need the values. ( It can be the same place you started from but you have to move a little).

Release the button and a menu will pop up.

Select " Copy Here as Values Only" - Done

There are also some other interesting items in this menu

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Roger that. Thanks guys. It was just an ad hoc sheet that she inherited that got out of control. I told her the best solution was a complete tear down and rebuild. That will save lots of headaches later.