Posted: 6/7/2016 4:10:52 PM EDT
|
In column A , when I type an employee name in I want it to auto fill an employee ID in column B.
Multiple employees with different ID numbers. Is this possible? New question I need to add a column that adds a time frame and converts it into minutes, The two times can be in separate columns. the minutes are in a separate column. Example 9:00- 5:00 480 |
|
Quoted:
If you have the employee info in another tab (or spreadsheet) you could do a vlookup. This is your answer. Keep the employees' names and ID numbers on a separate tab in the same workbook - it's a lot more convenient than linking to a whole other spreadsheet. |
|
At worst put the table of names and numbers on a new worksheet. Using a separate spreadsheet means keeping them always married or they will get out of synch almost immediately.
To start, put two columns with the info just to right and completely below the area where you are working in the worksheet until you get the basic logic worked out, then add the new worksheet tab with links. This prevents changes at the top where you are working through the analysis from changing the names table by inserting rows or columns into the data. Or if you aren't fancy, keep all the names and numbers on the same sheet. |
|
Quoted:
Do you want the employee IDs to be sequential? 101, 102, 103, etc? or are they already assigned IDs? Because I think you'd need to have a table or database with the info already entered if you need it to be non-sequential or pre-determined. -not an expert, BTW. Already issued numbers So make the 2 columns in a separate sheet at bottom with the information needed. Then vlookup?? |
|
Quoted:
This is your answer. Keep the employees' names and ID numbers on a separate tab in the same workbook - it's a lot more convenient than linking to a whole other spreadsheet. Quoted:
Quoted:
If you have the employee info in another tab (or spreadsheet) you could do a vlookup. This is your answer. Keep the employees' names and ID numbers on a separate tab in the same workbook - it's a lot more convenient than linking to a whole other spreadsheet. That would be my Plan A as well. At least finding a bad reference would tell me the employee list was out of date. But this certainly depends on the full details and use case. |
|
Quoted:
Already issued numbers So make the 2 columns in a separate sheet at bottom with the information needed. Then vlookup?? Quoted:
Quoted:
Do you want the employee IDs to be sequential? 101, 102, 103, etc? or are they already assigned IDs? Because I think you'd need to have a table or database with the info already entered if you need it to be non-sequential or pre-determined. -not an expert, BTW. Already issued numbers So make the 2 columns in a separate sheet at bottom with the information needed. Then vlookup?? Yup. The names will need to be alphabetical or vlookup will mess up, but the command is pretty simple when you do it a couple times. Don't be afraid to hit the function button (Fx) to get the information in the right spots. |
|
Quoted:
In column A , when I type an employee name in I want it to auto fill an employee ID in column B. Multiple employees with different ID numbers. Is this possible? Data Validation and lists. Create a separate list with all of your employees somewhere in your workbook. With data validation you can then simply select the employee from a dropdown. Then in the column next to it, say it's column B, use "IFERROR(IF(A2<>"",VLOOKUP(A2,Rangewithnamesandnumbers,2,FALSE),"Not Found") Modify as needed. Note if you use a table, it'll replace range references with table references, a la "tablename[column name]" etc. |
|
Quoted:
If you have the employee info in another tab (or spreadsheet) you could do a vlookup. Assuming you have some sort of unique ID that can link the two together you should just do a V-lookup. If you don't have a Unique ID/Primary Key linking the two together then you're fucked and nothing Excel can do will help you. |
|
Quoted:
Data Validation and lists. Create a separate list with all of your employees somewhere in your workbook. With data validation you can then simply select the employee from a dropdown. Then in the column next to it, say it's column B, use "IFERROR(IF(A2<>"",VLOOKUP(A2,Rangewithnamesandnumbers,2,FALSE),"Not Found") Modify as needed. Note if you use a table, it'll replace range references with table references, a la "tablename[column name]" etc. Quoted:
Quoted:
In column A , when I type an employee name in I want it to auto fill an employee ID in column B. Multiple employees with different ID numbers. Is this possible? Data Validation and lists. Create a separate list with all of your employees somewhere in your workbook. With data validation you can then simply select the employee from a dropdown. Then in the column next to it, say it's column B, use "IFERROR(IF(A2<>"",VLOOKUP(A2,Rangewithnamesandnumbers,2,FALSE),"Not Found") Modify as needed. Note if you use a table, it'll replace range references with table references, a la "tablename[column name]" etc. That only works assuming he has a seperate tab in which he already has the employees names and ID numbers |
|
Quoted:
That only works assuming he has a seperate tab in which he already has the employees names and ID numbers Quoted:
Quoted:
Quoted:
In column A , when I type an employee name in I want it to auto fill an employee ID in column B. Multiple employees with different ID numbers. Is this possible? Data Validation and lists. Create a separate list with all of your employees somewhere in your workbook. With data validation you can then simply select the employee from a dropdown. Then in the column next to it, say it's column B, use "IFERROR(IF(A2<>"",VLOOKUP(A2,Rangewithnamesandnumbers,2,FALSE),"Not Found") Modify as needed. Note if you use a table, it'll replace range references with table references, a la "tablename[column name]" etc. That only works assuming he has a seperate tab in which he already has the employees names and ID numbers Which is why I said the above in red. ETA: The list can be anywhere. Doesn't need to be a different tab. |
|
Quoted:
So I have the list with employee names In column b cells 4-16 Have the corresponding id number in column c 4-16 I transferred the drop down list from sheet 2 to where I need it in sheet 1 I cant seem to get the vlookup feature to carry over. 
Sounds like the range references got screwed up. Check the cell/table references in your formula. |
|
Quoted:
So I have the list with employee names In column b cells 4-16 Have the corresponding id number in column c 4-16 I transferred the drop down list from sheet 2 to where I need it in sheet 1 I cant seem to get the vlookup feature to carry over.
Vlookup would be on sheet one in the cells where you want the ID to be autofilled. It will reference the area on sheet two where the data is pulled from. So it should look like =VLOOKUP(A2,Sheet2!B4:C16,2,FALSE) Where A2 is whatever the name is that you are looking to match. |
|
Quoted:
Not necessary for OP's needs. Very useful formula, but in this instance it's building a clock when just knowing the time is needed. Quoted:
Quoted:
I'm going to be contrary and suggest using an INDEX MATCH formula Not necessary for OP's needs. Very useful formula, but in this instance it's building a clock when just knowing the time is needed. yeah, but if he's going to learn something new, he might as well learn something that is quite a bit more flexible |
|
Quoted:
yeah, but if he's going to learn something new, he might as well learn something that is quite a bit more flexible Quoted:
Quoted:
Quoted:
I'm going to be contrary and suggest using an INDEX MATCH formula Not necessary for OP's needs. Very useful formula, but in this instance it's building a clock when just knowing the time is needed. yeah, but if he's going to learn something new, he might as well learn something that is quite a bit more flexible In that case why not just go for full-on COM automation? 
If he's having trouble with VLOOKUP, he should get the hang of that first. |
|
Quoted:
Vlookup would be on sheet one in the cells where you want the ID to be autofilled. It will reference the area on sheet two where the data is pulled from. So it should look like =VLOOKUP(A2,Sheet2!B4:C16,2,FALSE) Where A2 is whatever the name is that you are looking to match. Quoted:
Quoted:
So I have the list with employee names In column b cells 4-16 Have the corresponding id number in column c 4-16 I transferred the drop down list from sheet 2 to where I need it in sheet 1 I cant seem to get the vlookup feature to carry over.
Vlookup would be on sheet one in the cells where you want the ID to be autofilled. It will reference the area on sheet two where the data is pulled from. So it should look like =VLOOKUP(A2,Sheet2!B4:C16,2,FALSE) Where A2 is whatever the name is that you are looking to match. Ok it works. Thank you! I had the semi colon as a comma is it normal for the #N/A to show up with no field selected in column A? |
|
Quoted:
Ok it works. Thank you! I had the semi colon as a comma is it normal for the #N/A to show up with no field selected in column A? Quoted:
Quoted:
Quoted:
So I have the list with employee names In column b cells 4-16 Have the corresponding id number in column c 4-16 I transferred the drop down list from sheet 2 to where I need it in sheet 1 I cant seem to get the vlookup feature to carry over.
Vlookup would be on sheet one in the cells where you want the ID to be autofilled. It will reference the area on sheet two where the data is pulled from. So it should look like =VLOOKUP(A2,Sheet2!B4:C16,2,FALSE) Where A2 is whatever the name is that you are looking to match. Ok it works. Thank you! I had the semi colon as a comma is it normal for the #N/A to show up with no field selected in column A? #N/A is returned if the value isn't found. Use =IFERROR(VLOOKUP(A2,Sheet2!$B$4:$C$16,2,FALSE),"") if you just want to show blanks instead of errors. ETA: Wrap your cell references in $ (dollar signs). That changes it to an absolute reference. Otherwise, when you drag the formula down, it'll change the row references in the formula (B4 to B5 to B6, C16 to C17 to C18) etc. |
|
Quoted:
If your data is in an ODBC compatible database, you can use PivotTable/MicrosoftQuery to pull it straight into Excel. This way, if the DB is changed, you can just hit refresh instead of having to re-do your sheet every time. Winner. As in"most robust answer that uses Excel." What you really want is an Access database. |
|
Quoted:
If your data is in an ODBC compatible database, you can use PivotTable/MicrosoftQuery to pull it straight into Excel. This way, if the DB is changed, you can just hit refresh instead of having to re-do your sheet every time. I remember when I first learned about PivotTables.... it was as if the clouds parted and trumpets sounded. 
|
|
Quoted:
I remember when I first learned about PivotTables.... it was as if the clouds parted and trumpets sounded. Quoted:
Quoted:
If your data is in an ODBC compatible database, you can use PivotTable/MicrosoftQuery to pull it straight into Excel. This way, if the DB is changed, you can just hit refresh instead of having to re-do your sheet every time. I remember when I first learned about PivotTables.... it was as if the clouds parted and trumpets sounded. No kidding. Doing summaries of spreadsheets or lists of data became *almost* magical. GIGO still applies, though. |
|
Quoted: yeah, but if he's going to learn something new, he might as well learn something that is quite a bit more flexible Quoted: Quoted: Quoted: I'm going to be contrary and suggest using an INDEX MATCH formula Not necessary for OP's needs. Very useful formula, but in this instance it's building a clock when just knowing the time is needed. yeah, but if he's going to learn something new, he might as well learn something that is quite a bit more flexible Yep. I would suggest match/index as its way more flexible, but if he's needing a very, very simple mapping, then I guess vlookup is sufficient. |
|
Quoted:
Winner. As in"most robust answer that uses Excel." What you really want is an Access database. Quoted:
Quoted:
If your data is in an ODBC compatible database, you can use PivotTable/MicrosoftQuery to pull it straight into Excel. This way, if the DB is changed, you can just hit refresh instead of having to re-do your sheet every time. Winner. As in"most robust answer that uses Excel." What you really want is an Access database. If he wants to get slick on a budget, MySql or SqLite could be used too. Access will likely already exist on his box if it's a corporate machine. |
|
Quoted:
If he wants to get slick on a budget, MySql or SqLite could be used too. Access will likely already exist on his box if it's a corporate machine. Quoted:
Quoted:
Quoted:
If your data is in an ODBC compatible database, you can use PivotTable/MicrosoftQuery to pull it straight into Excel. This way, if the DB is changed, you can just hit refresh instead of having to re-do your sheet every time. Winner. As in"most robust answer that uses Excel." What you really want is an Access database. If he wants to get slick on a budget, MySql or SqLite could be used too. Access will likely already exist on his box if it's a corporate machine. Introducing a new Access app to a corporate environment, however small and limited, should be a capital crime. |
|
Quoted:
Introducing a new Access app to a corporate environment, however small and limited, should be a capital crime. Quoted:
Quoted:
Quoted:
Quoted:
If your data is in an ODBC compatible database, you can use PivotTable/MicrosoftQuery to pull it straight into Excel. This way, if the DB is changed, you can just hit refresh instead of having to re-do your sheet every time. Winner. As in"most robust answer that uses Excel." What you really want is an Access database. If he wants to get slick on a budget, MySql or SqLite could be used too. Access will likely already exist on his box if it's a corporate machine. Introducing a new Access app to a corporate environment, however small and limited, should be a capital crime. I do not ever recommend Access. A lot of my work prior to my current clients dealt with getting people OFF of Access. Very often it was the same story - someone came on board who knew a couple of things about Access, maybe even a little VBA, wowed a lot of people because they could demonstrate an 'Update query' or 'For Each Loop' and won people over to the idea that Access is a great system. Ultimately, they weren't aware of its limitations, slowness, and 'bulkiness' and it impeded the flow of business, not enhanced it. Many corporate machines do not have it installed. Some may, but a lot of images don't include it because Access is a separate license cost. I've been in a few environments where it was a special request item only. YMMV, but I avoid Access like the plague. MySQL is a good alternative, but if sticking to the MS route, SQL Server Express blows access out of the water. |
|
Quoted: If he wants to get slick on a budget, MySql or SqLite could be used too. Access will likely already exist on his box if it's a corporate machine. Quoted: Quoted: Quoted: If your data is in an ODBC compatible database, you can use PivotTable/MicrosoftQuery to pull it straight into Excel. This way, if the DB is changed, you can just hit refresh instead of having to re-do your sheet every time. Winner. As in"most robust answer that uses Excel." What you really want is an Access database. If he wants to get slick on a budget, MySql or SqLite could be used too. Access will likely already exist on his box if it's a corporate machine. |
|
Quoted: Ok another question I need to add a column that adds a time frame and converts it into minutes, The two times can be in separate columns. the minutes are in a separate column. Example 9:00- 5:00 480 |