SoftMaker.com

Hello Planners:

I have a small spreadsheet -- more of a data-base, in fact. It is about 10 columns across, and in the rows are surnames, then firstnames, then other columns with information in them.

The very first column is just numbers 1, 2, 3, etc. and represents the order in which the names have been entered. But instead of typing in the numbers, I put 1 in the first cell, then the next one down is =a1+1 (which gives 2), the next is =a2+1 (which gives 3) , the next is =a3+1 (which gives 4), etc. To get this list, I copied the the formula =a1+1 and pasted it all the way down the column. So all the numbers appear, even though they are still waiting for date to be entered in their rows.

Now I would like to sort Column 2 (surname) with the second sort being Column 3 (firstname), and keep all the information related to each name with it. The columns to the right of the surname, firstname are no problem; they all sort with their related data. However, the first column, with the automatic numbering, does not keep the numbers correctly; in otherwords instead of them appearing something like ; 15, 2, 17 21, etc: (ie, each number attached to the Surname, in the order that the names were entered) stay 1, 2, 3, 4, 5, etc. even though I have included that column in the sort.

So it seems that the cells which have reference to another cell, abide by the formula rather than the result of the formula. I am not completely sure of that, because none of the other cells in this particular sheet have cells referencing other ones.

So, my question is: how can I include the numbers in column 1 in the Sort, so that they stay with the data in their particular row?

Any words of clarity from the wise would be much appreciated.

_________________
Cheers,

Peter

You need to enter the numbers in the first column explicitly. You can do this as you go; alternatively, as your sheet is quite small, auto-fill will do this nicely. The following example may help.

Start with an empty sheet. Into the first (say) 10 rows of Column B, put alphabetic data, making sure that the order is random (just as you would enter your names etc.).

In cell A1, enter 1. Click on A1 to re-select it, then drag the small square handle in the bottom right hand corner of the cell frame down to the last row in which there is data in column B. If that is the 10th row, the first 10 rows in column A will now be filled with the numbers 1 to 10.

When you sort this data on Column B, the Column A entries will remain with their respective column B entries, which is what you wish.

You can add further entries below the existing data, start a new set of numbering with the next number in the Column A sequence (11 in my example), and then continue as before, sorting the whole sheet when the data is entered. If the next number in the sequence becomes difficult to remember, place the formula MAX(A1:A500) somewhere convenient in your sheet; this formula (which assumes, of course, that your sheet has 500 rows or fewer) gives you the highest number entered so far in Column A, and you need to enter the next one.

I hope this helps.

David