I'm building a list showing employees, hire date, length of employment, and
wage. The list is alphabetized by last name. Format for Today's Date and Hire
Date was selected as m/d/yr. In the length column, I subtracted hire date
from today's date (=A1-A2) and and formatted the cells to show as a number. I
haven't calculated any of the Length numbers manually but comparing the
numbers to seniority they appear to be correct. Now I want to sort the list
by the numbers in the length column. When I follow the instructions in Help
for sort 0-9 or 9-0 by clicking a cell in the column and selecting AZ or ZA,
most of my data in the Length column changes to #REF and one changes to
#####. The order of my list is changing but the order of the new order isn't
clear to me. Some of my Length cells are blank for now because I won't have
access to that info til tomorrow. The blank cells are staying blank and are
at the end of both sort lists. Sure would enjoy any guesses for what I am
doing wrong. Thanks, K.

The A-Z or Z-A buttons only sort the currently selected column(s), using the
first one as sort order. In your case, if you selected the column with the
length of assignment, it got sorted and the formulas where screwed up , I
suppose.

By the way, should not your formula be Length: = B1-A1, as you should use
cells on the same row?

In your case, select the whole set of data to be sorted (or only one cell if
your range has no blank rows or columns), and use the menu option Data
Sort, then select the proper field(s) -- up to three -- for sorting.

Stephane.

Thank You Stephane for considering and responding to my post. Actually I gave
(=A1-A2) only as an example. My actual formulas are =B1-B4, =B1-B5, =B1-B6,
etc. All the dates are in the 'B' column. This morning I added in the missing
data and attempted the sort - no good. Then I added a column where I manually
copied over some of the Length data. Then I attempted to sort via Data - Sort
- Sort By - Length 2. It worked fine, but screwed up the column with the
formulas deriving the length figures. Then I attempted to sort Length 2 by AZ
and ZA. That was similarly successful too. Obviously you cannot sort data
derived by a formula, it must be pure data. Copying leaves room for error,
and takes longer, but at least I have it working now. Appreciate your help
Stephane. You got me this far. Thanks, K.

"Stephane Quenson" wrote:

The A-Z or Z-A buttons only sort the currently selected column(s), using the
first one as sort order. In your case, if you selected the column with the
length of assignment, it got sorted and the formulas where screwed up , I
suppose.

By the way, should not your formula be Length: = B1-A1, as you should use
cells on the same row?

In your case, select the whole set of data to be sorted (or only one cell if
your range has no blank rows or columns), and use the menu option Data
Sort, then select the proper field(s) -- up to three -- for sorting.

Stephane.