Locking rows across multiple sheets

[JDubba]

I'm using Excel 2003 and am having a problem locking rows across
multiple sheets. For example, I have two separate sheets in the same
workbook to track client jobs. Sheet 1 contains job status by job while
sheet 2 contains additional related job information by month. Column
one of sheet 2 references column one of sheet 1. However, all of the
other data in both sheets is independent.

My problem is that when I sort sheet 1 I would like the data in sheet 2
to sort as well, but maintain the integrity of the rows. If Job Alpha
is in row one in Sheet 1, then, after sorting, Job Alpha ends up in row
five, the reference to Job Alpha in column one of sheet 2 will be in
row five as well. However, the data corresponding to Job Alpha that was
in the other columns of row one on sheet 2 will still be in row one.
That is, the entire row will not sort together when one value is
sorted.

Is there a way to lock the rows in sheet two so they sort together when
sorted in sheet 1?
Sorry for the long and confusing explanation. Thanks in advance...

 

[Dave Peterson]

You might be able to do something using a combination of =indirect() and
=vlookup()'s, but I think it's filled with problems.

I think if I had to do this type of thing, I'd set up a macro (or a few macros)
that would sort each sheet following the same rules.

 

[JDubba]

Thanks Dave. I tried using the vlookup in various ways but it proved to
be very messy. I don't have any experience with macros but I'll give it
a shot and see what I can come up with.