filling data in based on other cells

[kijutsu]

Hi,


I have 2 spreadsheets - the first contains all the client information (name, address, phone, etc.) as well as a client ID number.

The second spreadsheet is for specific clients only, when they move into the second stage of the program they will go on this spreadsheet.

I want to be able to enter just the client ID in the second spreadsheet and have it complete the rest of the information automatically.

The first and second spreadsheet won't have all the ID's / be all in the same order as they won't all move across at the same time.
Is there any way to do this?

 

[John Davis]

Hi,


I have 2 spreadsheets - the first contains all the client information (name, address, phone, etc.) as well as a client ID number.

The second spreadsheet is for specific clients only, when they move into the second stage of the program they will go on this spreadsheet.

I want to be able to enter just the client ID in the second spreadsheet and have it complete the rest of the information automatically.

The first and second spreadsheet won't have all the ID's / be all in the same order as they won't all move across at the same time.
Is there any way to do this?

Maybe like this?

Sheet1

A B C D
Client ID Name Address Phone
1 John Newark 973
2 Will Paterson 201
3 Rob AP 732


Sheet2
A B C D
ID Name Address Phone


Formulas in Sheet2

B2 =IF(ISNA(VLOOKUP(A2,Sheet1!$A$3:$D$5,2,FALSE)),"",VLOOKUP(A2,Sheet1!$A$3:$D$5,2,FALSE))

C2 =IF(ISNA(VLOOKUP(A2,Sheet1!$A$3:$D$5,3,FALSE)),"",VLOOKUP(A2,Sheet1!$A$3:$D$5,3,FALSE))

D2=IF(ISNA(VLOOKUP(A2,Sheet1!$A$3:$D$5,4,FALSE)),"",VLOOKUP(A2,Sheet1!$A$3:$D$5,4,FALSE))


Cells B2:D2 copied down.