Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple cell values to be drawn from single cell/id.
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 ------------------------------------------------------------------------ El Shish's Profile: http://www.excelforum.com/member.php...o&userid=37710 View this thread: http://www.excelforum.com/showthread...hreadid=573007 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple cell values to be drawn from single cell/id.
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 El Shish Wrote: 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 ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=573007 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple cell values to be drawn from single cell/id.
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 ------------------------------------------------------------------------ El Shish's Profile: http://www.excelforum.com/member.php...o&userid=37710 View this thread: http://www.excelforum.com/showthread...hreadid=573007 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple cell values to be drawn from single cell/id.
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 ------------------------------------------------------------------------ El Shish's Profile: http://www.excelforum.com/member.php...o&userid=37710 View this thread: http://www.excelforum.com/showthread...hreadid=573007 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple cell values to be drawn from single cell/id.
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. El Shish Wrote: 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 ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=573007 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple cell values to be drawn from single cell/id.
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 ------------------------------------------------------------------------ El Shish's Profile: http://www.excelforum.com/member.php...o&userid=37710 View this thread: http://www.excelforum.com/showthread...hreadid=573007 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple cell values to be drawn from single cell/id.
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) El Shish Wrote: 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 ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=573007 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple cell values to be drawn from single cell/id.
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 ------------------------------------------------------------------------ El Shish's Profile: http://www.excelforum.com/member.php...o&userid=37710 View this thread: http://www.excelforum.com/showthread...hreadid=573007 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple cell values to be drawn from single cell/id.
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: El Shish Wrote: 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 | +-------------------------------------------------------------------+ -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=573007 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple cell values to be drawn from single cell/id.
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. -- El Shish ------------------------------------------------------------------------ El Shish's Profile: http://www.excelforum.com/member.php...o&userid=37710 View this thread: http://www.excelforum.com/showthread...hreadid=573007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking up multiple values for a single name | Excel Worksheet Functions | |||
How can I enter multiple paragraphs in a single Excel cell? | Excel Discussion (Misc queries) | |||
Can I run Excel LookUp on a single cell through multiple sheets | Excel Worksheet Functions | |||
How do I Auto-Filter with multiple values in a cell in Excel? | Excel Discussion (Misc queries) | |||
How to create Multiple Conditional Formulas in a single cell? | Excel Discussion (Misc queries) |