|
Can you help to find the right formula?
Hi,
I published this message on the Programming forum, but I'm not sure if the solution may come out from here as well, so... I have two WS, say WS1 and WS2 WS1 A B C D E 1 GARY MIK JOHN 1000 700 2 GARY SANDY BRIAN 500 300 The data in column D & E are values that a user inputs. When the user inputs D1, I want 1000 to go to WS2 where at row level the same 3 constants can be found. So, 1000 should go where in WS2 there are rows with the three words GARY, MIK, JOHN. WS2 C D E F G 1 ANDY ELLEN KEVIN 2 GARY SANDY BRIAN 3 GARY MIK JOHN 1000 In this way any value that the user inputs in WS1 D or E columns will go automatically into WS2 where the three combination of values are found at row level. Please, bear in mind that although the WS1 columns D and E and WS2 columns F and G have respectively the same heading name. Once again, if user enters in WS1 the value for E2, then 300 should automatically end up into WS2 G2. I dont't know if this can be done programmatically or by a particular fx function in Excel, but I'm sure you can help. Thank you Metallo |
On WS1 in an empty column (say H) use =A1&B1&C1 and copy down the column as
needed On WS2 in H1 use =MATCH(A1&B1&C1,ws1!$H$1:$H$20,FALSE) and copy Then in D1 use =IF(ISNA(H1),"",INDEX(ws1!$D$1:$D$20,ws2!H1)) best wishes Bernard "Metallo" wrote in message ... Hi, I published this message on the Programming forum, but I'm not sure if the solution may come out from here as well, so... I have two WS, say WS1 and WS2 WS1 A B C D E 1 GARY MIK JOHN 1000 700 2 GARY SANDY BRIAN 500 300 The data in column D & E are values that a user inputs. When the user inputs D1, I want 1000 to go to WS2 where at row level the same 3 constants can be found. So, 1000 should go where in WS2 there are rows with the three words GARY, MIK, JOHN. WS2 C D E F G 1 ANDY ELLEN KEVIN 2 GARY SANDY BRIAN 3 GARY MIK JOHN 1000 In this way any value that the user inputs in WS1 D or E columns will go automatically into WS2 where the three combination of values are found at row level. Please, bear in mind that although the WS1 columns D and E and WS2 columns F and G have respectively the same heading name. Once again, if user enters in WS1 the value for E2, then 300 should automatically end up into WS2 G2. I dont't know if this can be done programmatically or by a particular fx function in Excel, but I'm sure you can help. Thank you Metallo |
Bernard,
Thank for your reply. Could you please be more specific, I cannot have your suggestion to work. Every row has got 3 values that make the row unique, in this case any of the 3 names combination. I can understand what you do in WS1. I cannot understand what you suggest for WS2, maybe you can explain? Thank you Metallo "Bernard Liengme" wrote in message ... On WS1 in an empty column (say H) use =A1&B1&C1 and copy down the column as needed On WS2 in H1 use =MATCH(A1&B1&C1,ws1!$H$1:$H$20,FALSE) and copy Then in D1 use =IF(ISNA(H1),"",INDEX(ws1!$D$1:$D$20,ws2!H1)) best wishes Bernard "Metallo" wrote in message ... Hi, I published this message on the Programming forum, but I'm not sure if the solution may come out from here as well, so... I have two WS, say WS1 and WS2 WS1 A B C D E 1 GARY MIK JOHN 1000 700 2 GARY SANDY BRIAN 500 300 The data in column D & E are values that a user inputs. When the user inputs D1, I want 1000 to go to WS2 where at row level the same 3 constants can be found. So, 1000 should go where in WS2 there are rows with the three words GARY, MIK, JOHN. WS2 C D E F G 1 ANDY ELLEN KEVIN 2 GARY SANDY BRIAN 3 GARY MIK JOHN 1000 In this way any value that the user inputs in WS1 D or E columns will go automatically into WS2 where the three combination of values are found at row level. Please, bear in mind that although the WS1 columns D and E and WS2 columns F and G have respectively the same heading name. Once again, if user enters in WS1 the value for E2, then 300 should automatically end up into WS2 G2. I dont't know if this can be done programmatically or by a particular fx function in Excel, but I'm sure you can help. Thank you Metallo |
Hi,
I forgot to mention something important, the values to match are not contiguous as erroneously shown below. Therefore, the table is like this: WS1 A B C D E F 1 GARY X MIK JOHN 1000 700 2 GARY Y SANDY BRIAN 500 300 Only the combination of A, C and D can identify a unique row. Also to remember that the columns in WS2 are not the same as in WS1, it's a different format. Thanks Metallo "Metallo" wrote in message ... Bernard, Thank for your reply. Could you please be more specific, I cannot have your suggestion to work. Every row has got 3 values that make the row unique, in this case any of the 3 names combination. I can understand what you do in WS1. I cannot understand what you suggest for WS2, maybe you can explain? Thank you Metallo "Bernard Liengme" wrote in message ... On WS1 in an empty column (say H) use =A1&B1&C1 and copy down the column as needed On WS2 in H1 use =MATCH(A1&B1&C1,ws1!$H$1:$H$20,FALSE) and copy Then in D1 use =IF(ISNA(H1),"",INDEX(ws1!$D$1:$D$20,ws2!H1)) best wishes Bernard "Metallo" wrote in message ... Hi, I published this message on the Programming forum, but I'm not sure if the solution may come out from here as well, so... I have two WS, say WS1 and WS2 WS1 A B C D E 1 GARY MIK JOHN 1000 700 2 GARY SANDY BRIAN 500 300 The data in column D & E are values that a user inputs. When the user inputs D1, I want 1000 to go to WS2 where at row level the same 3 constants can be found. So, 1000 should go where in WS2 there are rows with the three words GARY, MIK, JOHN. WS2 C D E F G 1 ANDY ELLEN KEVIN 2 GARY SANDY BRIAN 3 GARY MIK JOHN 1000 In this way any value that the user inputs in WS1 D or E columns will go automatically into WS2 where the three combination of values are found at row level. Please, bear in mind that although the WS1 columns D and E and WS2 columns F and G have respectively the same heading name. Once again, if user enters in WS1 the value for E2, then 300 should automatically end up into WS2 G2. I dont't know if this can be done programmatically or by a particular fx function in Excel, but I'm sure you can help. Thank you Metallo |
Hi,
Can anobody help? Metallo "Bernard Liengme" wrote: On WS1 in an empty column (say H) use =A1&B1&C1 and copy down the column as needed On WS2 in H1 use =MATCH(A1&B1&C1,ws1!$H$1:$H$20,FALSE) and copy Then in D1 use =IF(ISNA(H1),"",INDEX(ws1!$D$1:$D$20,ws2!H1)) best wishes Bernard "Metallo" wrote in message ... Hi, I published this message on the Programming forum, but I'm not sure if the solution may come out from here as well, so... I have two WS, say WS1 and WS2 WS1 A B C D E 1 GARY MIK JOHN 1000 700 2 GARY SANDY BRIAN 500 300 The data in column D & E are values that a user inputs. When the user inputs D1, I want 1000 to go to WS2 where at row level the same 3 constants can be found. So, 1000 should go where in WS2 there are rows with the three words GARY, MIK, JOHN. WS2 C D E F G 1 ANDY ELLEN KEVIN 2 GARY SANDY BRIAN 3 GARY MIK JOHN 1000 In this way any value that the user inputs in WS1 D or E columns will go automatically into WS2 where the three combination of values are found at row level. Please, bear in mind that although the WS1 columns D and E and WS2 columns F and G have respectively the same heading name. Once again, if user enters in WS1 the value for E2, then 300 should automatically end up into WS2 G2. I dont't know if this can be done programmatically or by a particular fx function in Excel, but I'm sure you can help. Thank you Metallo |
| All times are GMT +1. The time now is 12:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com