If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. . .. |
#2
|
|||
|
|||
Frank. Sorting with references to other sheets?
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. . . |
Thread Tools | |
Display Modes | |
|
|