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!A150,2,FALSE) it will return Surname
put in C1 of sheet2 =VLOOKUP(A1,sheet1!A150,3,FALSE) it will return
First name
put in D1 of sheet2 =VLOOKUP(A1,sheet1!A150,4,FALSE) it will return
Salary.
you can change references and ranges according to your data.

hope this would help

 

[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 A5450 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.

 

[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 replacin
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 t
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 anythin
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-
sheet1). suppose you have data in sheet named PlayerData and you wan
to put Id's in sheet named PlayerID to return the respective info. the
change the function as follows.

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

 

[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:

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?

+-------------------------------------------------------------------+
|Filename: VLOOKUP.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5229 |
+-------------------------------------------------------------------+

 

[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