Locking rows across multiple sheets

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...

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 wrote:
>
> 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

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.

Dave Peterson wrote:
> 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 wrote:
> >
> > 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