cell referencing problems when sorting data

[am]

Hi,
When I perform a data sort on a worksheet two of my columns don’t sort with
the rest of the data.
For example a sort by first name changed to a sort by surname means these
two columns of cells still reference the original cell
='Employee db - Master'!H2 should change to ='Employee db - Master'!H43
after the sort but it doesn’t, it stays the same and references the wrong
data.
I’ve tried $H$2 that doesn’t work

How can I keep the cell pointing to the correct data in through a sort?

Many thanks


However, if I cut and paste cell B2 in sheet2, then sheet1 A1 follows
sheet2B2 anywhere I put it.

How can I keep cell A1 pointing to the data in B2 through a sort?

 

[Bernie Deitrick]

am,

The reference to the cell should move: if your formula

='Employee db - Master'!H2

was in a cell on row 3, and everything is moved to row 5, then the same formula should appear in row
5.

If, however, you expect that formula to stay on Row 3, then just do not select the column with the
formulas prior to your sorting.


HTH,
Bernie
MS Excel MVP

 

[am]

But it doesn't move, it references the wrong data, I am selecting all the
relevant data

 

[Bernie Deitrick]

am,

For Excel versions up to 2003 (I cannot speak to 2007), the reference to a cell on another sheet
will move when the table is sorted, and a reference to a cell on the same sheet will not appear to
move unless all the references are absolute: $H$2, then $H$3. etc.

Are you selecting the entire range before the sort? If you select one cell, and use Data / Sort,
Excel will select the current cell's region, so if you have entirely blank columns or rows in
between, you may not be picking up the range with the formulas.

HTH,
Bernie
MS Excel MVP

 

[Arvi Laanemets]

From your description it's obvious, you are sorting only a part of table -
maybe a single column.

Before performing sorting, be sure there is no gaps (empty rows or columns)
in your table - then when selecting a single cell from table, sort operation
sorts whole table automatically. When you leave any gap into table, the only
way to sort properly is to select the whole table manually.

Partial sorting will corrupt your table irrecoverably - your data become
meaningless gibberish.

Arvi Laanemets

 

[am]

Bernie: thanks, I am selecting the whole worksheet, the cell reference isn't
on another sheet, I've tried the $ symbol but still throws up the same
problems and I don't have any hidden blank columns. It's excel version 2003.

I appreciate your help

 

[ShaneDevenshire]

Hi,

First, I don't understand, you say the cell reference is not on another
sheet, then what do the formulas contain references to the sheet name? That
only happens when the formula and it's reference are on different sheets
unless you manually type them?

Suppose you are on Sheet1 with names in column A and B and the formula in
column C =A1&" "&B1

If you select all the data in the columns A:C and sort the formula opposite
the new position of the name that was in cell A1, say it sorted to row 4 will
read
=A4&" "&B4
Which is the correct formula or the wrong formula depending on your point of
view. The formula does not refer to A1 and B1 any more, is that wrong? But
it does product results that are consistant with the row they are on, is that
wrong?

If you change the formulas to read =A$1&" "&B$1 (in cell C1) and then sort
the data, the formulas continue to refer to A1 and B1 although they are now
on row 4, perhaps. Is this wrong? That depends on your point of view. Now
the data on row 4 is not self consistent, but is that wrong? The name in C4
is not the concatenation of the names in A4 and C4 but those in A1 and B1.

I doubt this help. Why don't you show three lines of data with column
letters, for example,

A B C
1 First Name Last Name Full Name
2 Shane Devenshire =A2&" "&B2
3 ...

 

[am]

Shane thank you so much, your suggestions made me look at it differently and
I've fixed the error, thank you

 

[flano 77]

Mate,

I have this same problem. It's killing me. How did you resolve it in the end?

I have names typed in Col A, then a bunch of formula's in rows B-Z which bring data from other sheets using the Col A data as lookup reference for sumif formula.

I have all sheet filtered. But when I sort by a column it sorts so that my formula in row 5 now looks at the Col A data in say row 97. I want these formula's in Col B-Z to stay referencing to the row they are in no matter how I sort.

Does this make sense? Any suggestions greatly appreciated.

Thanks,
Flano..

 

[D Zandveld]

Shane

I'm having a similar problem to this, but your answer (below) doesn't seem
to work.

I have 2 worksheets, lets call them 'Summary' and 'Data'

Cell A6 in Summary is a reference to A2 in Data (eg. =Data!A2)
When I sort Data (selecting all columns, Sort, Ascending), the reference in
Summary continues to point to A2 in Data.

I want the reference to move with the sorted data.

I have the data in both sheets formatted as 'General'.

Any ideas?