Excel - Sort Across Two Sheets

[FlixMixIntern]

I have an Excel file with two sheets. One column in Sheet2 pulls data from Sheet1 using a formula like this...
=Sheet1!A1

I am having a problem with sorting. I can easily sort Sheet1 using the sort option, but I also want to be able to simultaneously sort the corresponding data in Sheet2. How can I sort across both of these worksheets.

I am using Excel XP. Thanks.

Cameron...

 

[carrr]

If your mirroring Sheet 1 using the fix xlhelp gave you in the last post, Sheet2 data should reflect that on Sheet1.
If you sort Sheet1, Sheet2 sorts with it.

 

[FlixMixIntern]

That is correct, it does sort the columns that I am mirroring, but it does not sort the other columns in the worksheet. What I'd like to know is how to sort those other columns. Thanks.

 

[carrr]

Ok, you're referencing corresponding data on each line, no?
Is the data on sheet2 in columns b, c, d etc. the same as the data from sheet1, or do you have entirely different data , save the column you're mirroring?

 

[FlixMixIntern]

Columns b,c,d,etc are completely different from sheet1.

 

[carrr]

AFAIK, you'll have to sort on each sheet.

 

[Zathras]

Without a clearer picture of what the 2 sheets look like, I would guess that an application of VLOOKUP formulas is what you are looking for. Possibly using third sheet containing the actual data that you want to show correlated to the new values in the first column of sheet 2 is what you are looking for.

if sheet 1 has
A B
CUSTNO CITY
1234 LOS ANGELES
1232 NEW YORK
1230 CHICAGO

and sheet 2 has
A B
CUSTNO REGION
=Sheet1!A1 =VLOOKUP(A2,Sheet3!$A2:$B4,2,0)
=Sheet1!A2 =VLOOKUP(A3,Sheet3!$A2:$B4,2,0)
=Sheet1!A3 =VLOOKUP(A4,Sheet3!$A2:$B4,2,0)

and sheet 3 has
A B
CUSTNO REGION
1234 4
1232 2
1230 3


you can sort sheet 1 and sheet 2 will show the correct region.

Note1: Perhaps the data from sheet 3 can also be stored in sheet 1.

Note2: If there are a great many columns and rows in sheet 2, the recalculation after sorting may slow down to an unacceptable level. In that case one column of VLOOKUP combined with use of the INDEX formula in the other columns will speed things up again.