UNSOLVED
Sort question for MSoffice Basic Edition 2003
Is there a way to sort in excel (windows XP) to get the following results
Table A
Col A Col B
1 10
2 20
4 40
5 50
3 30
Table B
Col F Col G
=a1 =b1
=a2 =b2
=a3 =b3
=a4 =b4
Now sort the 5 values in Table A without changing the values in Table B.
Table B should show - after the sort (1,2,4,5 and 10,20,40,50).
Is this possible??
Any ideas?
Message Edited by Dinman32 on 12-19-200603:59 AM
Message Edited by Dinman32 on 12-19-200604:10 AM
160
0
Posted December 22nd, 2006 23:00
You could put table B on a new worksheet.160
0
Posted December 24th, 2006 00:00
Delierious wrote:
You could put table B on a new worksheet.
Even if you do this, when you sort table A, the values in table B will change.I too have run into the problem and not realizing that Excel did this has caused some problems for me. Note: if you sort table B, the reference cell moves with the sort.
160
0
Posted December 24th, 2006 11:00
Yes, you're right.
This leaves a blank line, but works otherwise:
Column F
=IF(A1=3,"",A1)
Column G
=IF(A1=3,"",B1)Message Edited by Delierious on 12-24-200607:33 AM
160
0
Posted December 25th, 2006 18:00
Thx for the Help - but it doesnot seem like it gets the job done.In my real situation table A is in alphabetical name order and about 10-15 names are added each month for 12 months. Table B is really made up of 12 different tables (one per month) that shows what names & associated data was added for that specific month no matter what the name.Also I want the ability to change data in table A and have table B,C,D..... show the changes.(what I do now is insert the new record in table A and all else works ok)Any other ideas??160
0
Posted December 25th, 2006 21:00
OK,
What I have done is made a spreadsheet of 13 worksheets. The first worksheet is called Data and has columns,
A Surname,
B Name,
C Date Joined,
D Address,
E Subscription Paid (Date),
F Notes,
G Month Notes entered. This column is formatted as Text.
The vital information is held in columns A, B, F and G.
The next 12 worksheets are named Jan, Feb, Mar, Apr, May, etc.
Worksheet Jan
Has
3 Columns
A: =IF(Data!$G2="Jan", Data!A2,"")
B: =IF(Data!$G2="Jan", Data!B2,"")
C: =IF(Data!G2="Jan", Data!F2,"")
(ignore the @~#! winking smileys, I don’t know how to get rid of them – the final part of the formula is - quotes quotes end brackets)
The other worksheets will have the same formulae but with “Feb”, “Mar” “Apr” etc,. inserted in place of “Jan”
Drag the formulae down each worksheet as far as you can.
If you then enter the “Notes” in the Data sheet and the month, in the form that agrees with the formulae, you will have a note of when the note was made set against a name.
If you want a greater spreadsheet, use JAN07 in column D of the Data worksheet and increase the number of worksheets accordingly.
The downside of this is that unless you just add notes (rather than replacing them) in Column G of Data, the history will be lost.
The upside is that if you insert a name alphabetically between, say, row 45 and Row 46, it will not matter because all other worksheets will be updated.
Another downside is that each month worksheet will have blank rows.Message Edited by Delierious on 12-25-200605:22 PM
abach
1728 Posts
160
0
Posted December 19th, 2006 15:00