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 ...

--
Thanks,
Shane Devenshire


"am" wrote:

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



"Arvi Laanemets" wrote:

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" wrote in message
...
Hi,
When I perform a data sort on a worksheet two of my columns dont 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 doesnt, it stays the same and references the wrong
data.
Ive tried $H$2 that doesnt 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?