Sorting with references to other sheets?

[Mark M]

I'm only an occasional user of Excel (Excel 97). So
hopefully I'm just missing something simple.

I have two lists on two separate sheets.

Disconnects

A B C
Id Desc Result
D.1 I'm getting wet R.1 <---- Requirements!A2
D.2 I'm hungry R.2 <---- Requirements!A3

Requirements

A B C
Id Desc Origin
R.1 I must go inside D.1 <---- Disconnects!A2
R.2 I must eat D.2 <---- Disconnects!A3

My problem occurs when I sort the requirements sheet by
the description. Suddenly my "I'm hungry" disconnects
references R.1 and "I must go inside". This is obviously
wrong.

I'd appreciate any help I can get with this. Thanks in
advance.

 

[Frank Kabel]

Hi
one way.
change the formula
=Requirements!A2

to
=INDEX(Requirements!A2:A100,MATCH(A1,Requirements!C2:C100,0))

and try again

 

[Mark M]

Thanks, Frank

As always with these things your solution works for the
simplest scenario, which is great, but my situation is a
little more complicated. I guess I should I have
elaborated more in my original post.

I need the ability to sort both sheets independently. One
of the problems I have with your solution is that both my
origin and result fields are references. If I want to sort
both sheets and use your formula, I run into a situation
of circular matching.

My other issue is that in the actual spreadsheet that I'm
having these problems with I don't always only have one
value in the reference columns. Sometimes my result column
in the disconnects sheet has the expression: =Requirements!
A111 & "," & Requirements!A112 & "," & Requirements!A113
& "," & Requirements!A114. This is necessary when a
particular disconnect results in more than one
requirement. It also works the other way where I might
have multiple disconnects resulting in a single
requirement. So obviously matching is out of the question.

Basically what I want to be able to do is to have my
references change dynamically with a sort just as they do
when I add or delete a row from one of the sheets.