sorting muliple sheets that reference

DaveQB · Power User Joined: 17 Apr 2004 Posts: 50 Location: Sydney

I hope i can explain this good

The first sheet i have in my spreadsheet is a list of name with additional info about the people. The second sheet reference the first name and surname column and then has other info about these people.

I have since added more people to the bottom and now want to alphabetically sort the first sheet (and second sheet) without messing up the spreadsheet.

Is there a way to do this ???

Sorting the first sheet leaves the second sheet referencing the incorrect names and 'corupts' the data.
_________________
OOo 1.1.5
OS: Kubuntu 5.10 "Breezy Badger"

denmarks · Posted: Thu Mar 30, 2006 9:22 am Post subject:

When you create a range always leave an extra line at the top and bottom so you can insert items. When you sort do not sort these 2 lines.

FIRST NAME
xxx
yyy
zzz
--END OF FIRST NAMES --

Define the 5 items as the range.
Always insert lines after the heading and before the last line.
Only sort data, not the header and last line.
_________________
Dennis Marks

Villeroy · Posted: Thu Mar 30, 2006 10:10 am Post subject:

The first sheet has one single line for each person whereas the second one has zero, one ore more lines referring to a person on the first sheet?
The references to first sheet are simple refs like "=$Sheet1.$A13"?

DaveQB · Power User Joined: 17 Apr 2004 Posts: 50 Location: Sydney

Villeroy,
Second sheet has 1 line per name/person like the first sheet.

Second sheet has references like this :

=Players.C24

Where 'Players' is the name of the first sheet.

denmarks,

I am confused how thats going to help.
_________________
OOo 1.1.5
OS: Kubuntu 5.10 "Breezy Badger"

Villeroy · Posted: Thu Mar 30, 2006 12:42 pm Post subject:

Using one row for each person: Why not everything in one table?
Then play with Window>Split, Window>Freeze or select some columns and call Data>Group..."by Columns". The latter adds some buttons above the headers allowing you to hide/unhide columns quickly.
If there is some reason for separate sheets and you are not familiar with databases:
Select col A and insert a new column.
Assumed labels in the first row, add 1, 2, 3 in A2:A4, select A2:A4 and drag down. Now you have a unique number for each item.
Apply the same to the other sheet, so the same number refers to the same person.
Instead of =$Sheet1.$Axxx use something like:
Formula B2 =VLOOKUP($A2;$Sheet1.$A$2:$G$65536;2;0)
Lookup the value in exactly ($) column A, relative to this row (missing $) in first column of exactly $Sheet1.$A$2:$G$65536 and return the value in the second column of that range (red 2). The 0 indicates, that the search column can be be unsorted. "1" would use a slightly different mode, which is not required in this case.
Getting a free number for a new entry:
=Max($A$1:$A$65536)+1
A database could handle this automatically.