Multiple cell values to be drawn from single cell/id.

[El Shish]

Am currently working on a spreadsheet in which i will require three cells' worth (from the same column) of information to be recognised by a single number/id.

For example
A1 - 101
B1 - Surname
C1 - First Name
D1 - Salary

Does anybody know of any possible way to make it so that by typing A1's value (101), the data from B1,C1,D1 all appear vertically?

Also, if it were possible to get this working across multiple worksheets.

Would really appreciate any help possible with this thread.

[starguy]

suppose you put A1's value in cell A1 of sheet2 then put in B1 of sheet2 =VLOOKUP(A1,sheet1!A1:D50,2,FALSE) it will return Surname
put in C1 of sheet2 =VLOOKUP(A1,sheet1!A1:D50,3,FALSE) it will return First name
put in D1 of sheet2 =VLOOKUP(A1,sheet1!A1:D50,4,FALSE) it will return Salary.
you can change references and ranges according to your data.

hope this would help

Am currently working on a spreadsheet in which i will require three cells' worth (from the same column) of information to be recognised by a single number/id.

For example
A1 - 101
B1 - Surname
C1 - First Name
D1 - Salary

Does anybody know of any possible way to make it so that by typing A1's value (101), the data from B1,C1,D1 all appear vertically?

Also, if it were possible to get this working across multiple worksheets.

Would really appreciate any help possible with this thread.

[El Shish]

Thanks, i'm trying that out right now.

I don't know if there is a simpler way of doing things or even if this is possible at all but is there a way of creating a sheet for individuals in which each person has a code or id. The surname, first name and salary are all linked to this id.

On a separate worksheet, i would like to create a system whereby i would enter the unique code and the relevant surname, first name and salary would all automatically appear in the subsequent vertical columns.

Am i being over ambitious and does a feature even exist without major work having to be done?

Thanks

[El Shish]

Or to clarify...

I currently have a worskheet that has sequential id numbers occupying cells A5 to A450, the surname occupies B5 to B450, first name C5 to 450 and salary occupying D5 to D450.

In an ideal world i would like to, in a separate worksheet only have to enter the individuals' id numer say in column b of a separate work sheet and have the surnmae, first name and salary all automatically appearing in columns, c,d and e.

Help with this would be greatly appreciated.

[starguy]

suppose you have data in sheet1 in a range A5:D450 and you want to put Id in sheet2 and all other related information should appear automatically.

if you put Id in cell A10 in sheet2 and you want cell B10 to show Surname, cell C10 to show First name and so on.
then put following formulas in sheet2
in cell B10 of sheet2 =VLOOKUP(A10,SHEET1!$A$5:$D$450,2,FALSE)
in cell C10 of sheet2 =VLOOKUP(A10,SHEET1!$A$5:$D$450,3,FALSE)
in cell D10 of sheet2 =VLOOKUP(A10,SHEET1!$A$5:$D$450,4,FALSE)
you can copy these formulas down and then they will return values for respective id in cells A11, A12, A13 and so on...

hope this would serve your purpose.

Or to clarify...

I currently have a worskheet that has sequential id numbers occupying cells A5 to A450, the surname occupies B5 to B450, first name C5 to 450 and salary occupying D5 to D450.

In an ideal world i would like to, in a separate worksheet only have to enter the individuals' id numer say in column b of a separate work sheet and have the surnmae, first name and salary all automatically appearing in columns, c,d and e.

Help with this would be greatly appreciated.

[El Shish]

Thanks for that...

I've adapted the formula so it looks as follows
=VLOOKUP(B6,PlayerID!$A$5:$D$450,2,FALSE)

B6 is the first cell where i would hope to put the ID thus replacing the A10 as you have suggested and PlayerID is the name of Sheet2.

Everything else I have left untouched but the formula doesn't seem to work. Entering the code in B6 and hitting return has no effect and in cells C6,D6,E6, the formula remains visible and nothing happens when entering the ID in B6.

Have i changed the formula too much do you think or is there anything you can see from the above formula that is preventing it from working correctly?

Thanks again for your time and patience.

[starguy]

instead of PlayerID enter the sheet name which contains your data (i-e sheet1). suppose you have data in sheet named PlayerData and you want to put Id's in sheet named PlayerID to return the respective info. then change the function as follows.

=VLOOKUP(B6,PlayerData!$A$5:$D$450,2,FALSE)
change bolded range in all formulas (first put correct sheet name instead of PlayerData)

Thanks for that...

I've adapted the formula so it looks as follows
=VLOOKUP(B6,PlayerID!$A$5:$D$450,2,FALSE)

B6 is the first cell where i would hope to put the ID thus replacing the A10 as you have suggested and PlayerID is the name of Sheet2.

Everything else I have left untouched but the formula doesn't seem to work. Entering the code in B6 and hitting return has no effect and in cells C6,D6,E6, the formula remains visible and nothing happens when entering the ID in B6.

Have i changed the formula too much do you think or is there anything you can see from the above formula that is preventing it from working correctly?

Thanks again for your time and patience.

[El Shish]

Ok, the ids are being entered into column b starting with b6

C6 contains the following formula
=VLOOKUP(B6,PlayerID!$A$5:$D$450,2,FALSE)
D6 contains the following formula
=VLOOKUP(B6,PlayerID!$A$5:$D$450,3,FALSE)
E6 contains the following formula
=VLOOKUP(B6,PlayerID!$A$5:$D$450,4,FALSE)

The sheet containing the ID and the individual's information is called PlayerID

Within that sheet A5-A450 contain the player's ID
B5-B450 contain the Player's Surname
C5-D450 contain the Player's First Name
D5-D450 cnntain the Player's Salary

Hopefully this means I have followed your advice correctly but its still not working and i'm not sure why.

Any ideas?

[starguy]

I have attached a sample workbook with these functions implemented and they work fine.

however attach a sample workbook or send to me for proper consideration:
>>[email protected]

Ok, the ids are being entered into column b starting with b6

C6 contains the following formula
=VLOOKUP(B6,PlayerID!$A$5:$D$450,2,FALSE)
D6 contains the following formula
=VLOOKUP(B6,PlayerID!$A$5:$D$450,3,FALSE)
E6 contains the following formula
=VLOOKUP(B6,PlayerID!$A$5:$D$450,4,FALSE)

The sheet containing the ID and the individual's information is called PlayerID

Within that sheet A5-A450 contain the player's ID
B5-B450 contain the Player's Surname
C5-D450 contain the Player's First Name
D5-D450 cnntain the Player's Salary

Hopefully this means I have followed your advice correctly but its still not working and i'm not sure why.

Any ideas?

[El Shish]

Hi,

I've fiddled a little and got the thing working.

Turns out i had it in formula auditing mode.

God, that makes me feel more than a little silly.

Anyhow, thanks very much for your help and time devoted.