Frank. Sorting with references to other sheets?

Thanks for your suggestion 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 using matching as part of the solution
would seem to be out of the question.

Basically what I need 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. I would think it
should be an option within Excel that I can turn on?

Do you have any other sugestions?


-----Original Message-----
Hi
one way.
change the formula
=Requirements!A2

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

and try again

--
Regards
Frank Kabel
Frankfurt, Germany
"Mark M" schrieb im Newsbeitrag
...
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.

.

..

Hi Mark
if I understood your issue correctly I doubt there's a solution to it,
sorry

--
Regards
Frank Kabel
Frankfurt, Germany

Mark M wrote:
Thanks for your suggestion 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 using matching as part of the solution
would seem to be out of the question.

Basically what I need 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. I would think it
should be an option within Excel that I can turn on?

Do you have any other sugestions?


-----Original Message-----
Hi
one way.
change the formula
=Requirements!A2

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

and try again

--
Regards
Frank Kabel
Frankfurt, Germany
"Mark M" schrieb im Newsbeitrag
...
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.

.

.