cross referencing data

[CP]

Hi and thanks for reading

I have 3 sheets (lets say 1, 2 and 3) each are identical in format - each
sheet refers to a seperate year.
a B C D
Client 20 34 54

A is common Client D= total of B and C

What I need to do is create sheet4 which shows the following

A B C D
Clients D sheet1 D sheet2 D Sheet3

Any help would be greatly appreciated

 

[Jacob Skaria]

Assuming you have data starting from Row 2

Sheet4 B2 would be =1!D2
Sheet4 C2 would be =2!D2
Sheet4 D2 would be =3!D2

Once you enter this 3 select the 3 cells and copy that down to the number of
clients/

If this post helps click Yes

 

[CP]

Thanks for the quick response

I am not sure this will work as the client column is not organised and also
they are not all the same - meaning a client may be in y1 and y2 but maybe
not y3 or any combination of the three.
I think I need some sort of lookup to link the clients, but have little idea
how to do it

 

[Jacob Skaria]

Assuming you have data starting from Row 2
and you have sheets named "1" "2" and "3"
try the below formula in Sheet "4" Cell B2 , C2, D2 after entering the
client name in Sheet4 A2

Sheet4 B2 would be =INDEX('1'!D,MATCH(A2,'1'!A:A,0))
Sheet4 C2 would be =INDEX('2'!D,MATCH(A2,'2'!A:A,0))
Sheet4 D2 would be =INDEX('3'!D,MATCH(A2,'3'!A:A,0))


If this post helps click Yes