Can you help to find the right formula?

  • Thread starter Thread starter Metallo
  • Start date Start date
M

Metallo

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
 
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
 
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
 
Hi,

Can anobody help?

Metallo

Bernard Liengme said:
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
Click to expand...
 
Back
Top