Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula to populate information from multiple cells in another workbook
Hello, I'm trying to create a spreadsheet for work to track employee check in times for safety reasons. What I am looking to do is have an employee open the safety spreadsheet, and by inputting a tech's employee ID, to have it automatically plot in his name, pager and manager (that would be linked from another spreadsheet, the Tech List spreadsheet). What I would like to know is how feasible/possible is it to create something like this? I have intermediate/advanced excel skills, but I'm just alright with formulas. I know I can link info from one book to another, but what I'd like to do is when I input a tech's ID in column A of the safety book, the formula searches the Tech List book for the tech's ID, finds it, then reads the tech's pager, manager and name from the same row as his ID would be on. How is this done? Thanks for any help anyone can give, sorry if I haven't been totally clear, let me know if there is any clarification needed! Tim -- Sullycanpara ------------------------------------------------------------------------ Sullycanpara's Profile: http://www.excelforum.com/member.php...o&userid=35879 View this thread: http://www.excelforum.com/showthread...hreadid=556716 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula to populate information from multiple cells in another workbook
Sullycanpara wrote: Hello, I'm trying to create a spreadsheet for work to track employee check in times for safety reasons. What I am looking to do is have an employee open the safety spreadsheet, and by inputting a tech's employee ID, to have it automatically plot in his name, pager and manager (that would be linked from another spreadsheet, the Tech List spreadsheet). What I would like to know is how feasible/possible is it to create something like this? I have intermediate/advanced excel skills, but I'm just alright with formulas. I know I can link info from one book to another, but what I'd like to do is when I input a tech's ID in column A of the safety book, the formula searches the Tech List book for the tech's ID, finds it, then reads the tech's pager, manager and name from the same row as his ID would be on. How is this done? Thanks for any help anyone can give, sorry if I haven't been totally clear, let me know if there is any clarification needed! Tim -- Sullycanpara ------------------------------------------------------------------------ Sullycanpara's Profile: http://www.excelforum.com/member.php...o&userid=35879 View this thread: http://www.excelforum.com/showthread...hreadid=556716 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula to populate information from multiple cells in another workbook
Nel post news:Sullycanpara.2a4y3n_1151537104.1645@excelforu m-nospam.com
*Sullycanpara* ha scritto: Hello, I'm trying to create a spreadsheet for work to track employee check in times for safety reasons. What I am looking to do is have an employee open the safety spreadsheet, and by inputting a tech's employee ID, to have it automatically plot in his name, pager and manager (that would be linked from another spreadsheet, the Tech List spreadsheet). What I would like to know is how feasible/possible is it to create something like this? I have intermediate/advanced excel skills, but I'm just alright with formulas. I know I can link info from one book to another, but what I'd like to do is when I input a tech's ID in column A of the safety book, the formula searches the Tech List book for the tech's ID, finds it, then reads the tech's pager, manager and name from the same row as his ID would be on. How is this done? Thanks for any help anyone can give, sorry if I haven't been totally clear, let me know if there is any clarification needed! Tim Hi Tim, I think the most suitable function for your needs is VLOOKUP. Check on line help and try to use the function. If should have any problem, then post again here. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula to populate information from multiple cells in another workbook
Thanks for the help, VLOOPUP is exactly what I wanted, and I've got it to work, sort of. The line of formula I'm using is: =VLOOKUP(A7,Tech_List!A7:D7,2,TRUE) The problem I am now having is that when I plot my tech ID into column A, the formula reads the information from the linked page on the exact same line as what is in the formula, (in the example, row 7). The problem is that if I'm inputting a tech ID on row 7 of the safety tracking spread sheet, but he's on row 128 on the tech list(for example) I want to have the safety tracking spreadsheet find his ID on row 128 of the tech list and return the corresponding information. I know it has to be something simple that I'm missing out on here, but what is it? Thanks ahead of time for the futher help! Tim -- Sullycanpara ------------------------------------------------------------------------ Sullycanpara's Profile: http://www.excelforum.com/member.php...o&userid=35879 View this thread: http://www.excelforum.com/showthread...hreadid=556716 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula to populate information from multiple cells in another workbook
Nel post
*Sullycanpara* ha scritto: Thanks for the help, VLOOPUP is exactly what I wanted, and I've got it to work, sort of. The line of formula I'm using is: =VLOOKUP(A7,Tech_List!A7:D7,2,TRUE) I think should be: =VLOOKUP(A7,Tech_List!A7:D7,2,FALSE) -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula to populate information from multiple cells in another workbook
Unfortunately, good tip, didn't think to change that, but unfortunately false didn't work. there has to be a way to tell the formula that it needs to read the info from the row that the information in the A column is giving it, but I'm not sure yet just how to tweak that ;) Thanks again for your suggestion, anything else will be quickly tried ;) Tim -- Sullycanpara ------------------------------------------------------------------------ Sullycanpara's Profile: http://www.excelforum.com/member.php...o&userid=35879 View this thread: http://www.excelforum.com/showthread...hreadid=556716 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula to populate information from multiple cells in another workbook
Your formula is only looking at *one* row in the Tech_List sheet, Row7.
Your formula has *no* range to search, just a single line! You must include the entire datalist so that the formula has a range to look at ... for example: =VLOOKUP(A7,Tech_List!A1:D100,2,0) The final zero is equivalent to "False", meaning you're looking for an exact match only. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sullycanpara" wrote in message ... Unfortunately, good tip, didn't think to change that, but unfortunately false didn't work. there has to be a way to tell the formula that it needs to read the info from the row that the information in the A column is giving it, but I'm not sure yet just how to tweak that ;) Thanks again for your suggestion, anything else will be quickly tried ;) Tim -- Sullycanpara ------------------------------------------------------------------------ Sullycanpara's Profile: http://www.excelforum.com/member.php...o&userid=35879 View this thread: http://www.excelforum.com/showthread...hreadid=556716 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula to populate information from multiple cells in another workbook
Using the newest tip, and seeing that yes, I'll definatly need to do that to search the entire database of tech info, so that is an important step. Problem is, is that I don't think the VLOOKUP is searching the "A" column for the tech ID, and I'm not sure what I need to change to get it to work. In my formula that I'm using, is for example: =VLOOKUP(A7,Tech_List!A1:D100,2,0) The A7 is the cell on the current page that is the tech ID that I want to search for the Tech_List! is telling excel to look on that sheet, A1:D100 is telling it the range of cells to look in The "2" is telling the formula what cell to reference in the table The "0" is the false indicator So am I understanding the formula correctly? If so, why does the VLOOKUP not actually LOOK for the tech ID (in the A7 cell ref as per the example), is there something else I need to add? Thanks again for everyone's help, I couldn't have gotten to this state of confusion without you! ;) Tim -- Sullycanpara ------------------------------------------------------------------------ Sullycanpara's Profile: http://www.excelforum.com/member.php...o&userid=35879 View this thread: http://www.excelforum.com/showthread...hreadid=556716 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula to populate information from multiple cells in another workbook
First of all:
<<<"The "2" is telling the formula what cell to reference in the table" Just change "cell" to "column". Next: As a test, key in yourself a tech ID into A7. In your datalist, do the same thing in any Column A cell, to make sure that the data matches *exactly*. Did your formula return the correct value from the second column of your datalist? Almost all problems of this nature are the result of mis-matched data. Either leading or trailing spaces, or hidden codes that are part of imported data. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Sullycanpara" wrote in message news:Sullycanpara.2a7x1o_1151675707.8176@excelforu m-nospam.com... Using the newest tip, and seeing that yes, I'll definatly need to do that to search the entire database of tech info, so that is an important step. Problem is, is that I don't think the VLOOKUP is searching the "A" column for the tech ID, and I'm not sure what I need to change to get it to work. In my formula that I'm using, is for example: =VLOOKUP(A7,Tech_List!A1:D100,2,0) The A7 is the cell on the current page that is the tech ID that I want to search for the Tech_List! is telling excel to look on that sheet, A1:D100 is telling it the range of cells to look in The "2" is telling the formula what cell to reference in the table The "0" is the false indicator So am I understanding the formula correctly? If so, why does the VLOOKUP not actually LOOK for the tech ID (in the A7 cell ref as per the example), is there something else I need to add? Thanks again for everyone's help, I couldn't have gotten to this state of confusion without you! ;) Tim -- Sullycanpara ------------------------------------------------------------------------ Sullycanpara's Profile: http://www.excelforum.com/member.php...o&userid=35879 View this thread: http://www.excelforum.com/showthread...hreadid=556716 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting a formula in multiple cells without changing the range | Excel Discussion (Misc queries) | |||
Formula for displaying in 1 cell Lowest value from multiple other cells | Excel Worksheet Functions | |||
Populate multiple cells using the same reference | Excel Worksheet Functions | |||
How do I protect formula cells on multiple sheets? | Excel Worksheet Functions | |||
How to populate formula in range of vertical cells to next colum | Excel Worksheet Functions |