Say I have a log sheet in Excel to keep track of the applications I enter into the system. On that log sheet I have to enter the Caseworker name, their caseload number, and their worker number. Is there any way to make the caseload and worker numbers enter automatically into their respective cells when I enter the caseworker name rather than having to type them in every time?
Thanks in advance!
If you can set up a list of caseworker information somewhere, then putting VLOOKUP functions into the caseload number and worker number cells should do the trick. Try searching the help (or googling,) under VLOOKUP and it shouldn’t be hard to find simple examples.
I’d do this under Microsoft Access, which is also a part of Microsoft Office. If you have Excel, you probably have Access as well.
Under Access, I’d make Caseworkername the indexfield that connects two databases: one with the Caseworker name and the applications, and one with the Caseworker name, their caseload number, and their worker number. Then link the two databases. That’s kids’ stuff in Access.
I would strongly recommend following the advise of chrisk, in my experience Access is a very poor tool that should only be used for huge piles of data. Excel is always the preferred option. I use both but lean heavily toward Excel which is one of the best pieces of software ever created. Access is merely functional.
As it happens, I’m a huge access geek and think that it’s a great piece of software… for people who can put in a little time to understand it. I don’t generally recommend moving spreadsheet info into access just for the sake of it though. 
In Access this would be child’s play. Just create the tables, plus a form to enter the data. The whole idea behind a realtional database like Access is to avoid having to type the same data over and over again. You could just enter the name and hours on the form, and get a query giving all the other information you need.
The vlookup function in Excel does the same thing. You create a reference table of data and lookup the values you want. It is quicker to do it in Excel.
I agree that Access does this easily, but whether or not to bother doing it with Access depends on two things:
And to flesh out chrisk’s suggestion, assuming you don’t have Access
1.create a table of each worker’s name, number and caseload wherever it’s convenient. If you want to look up information by the person’s name, make the name you want to lookup by the leftmost column of the table.
Sort the table by the name of the worker if that’s what you want to lookup stuff by.
In the sheet where you need the information to be entered, in the cell where you want the worker’s number to appear, type the following:
=VLOOKUP(, ,)
Place the cursor before the first comma, and click in the cell, where you typed (or will type) the person’s name.
Place the cursor in between the two commas, and highlight the table where you entered the reference material in step 1. Before each row number and column letter, type in a dollar sign, so if, after you highlight the table, it looks like this:
G6:M25
make it look like:
$G$6:$M$25
Finally, place the cursor after the last comma, and, counting the leftmost column of the lookup table as column 1, enter the corresponing column number for the worker’s number.
repeat steps 3-6 in the cell next door to the one where you entered the formula for the worker number to enter the caseload in that cell.
Fill down through the log sheet as necessary
VLOOKUP works to find a piece of information in a table where each vertically-stacked row is another record. If you have information stored in such a way that each record is in a column instead, use the otherwise nearly identical HLOOKUP function.
Answer to the OP’s specific question is yes, just use one of Excel’s lookup functions. Pretty simple. The larger question of Excel vs. Access depends on the intended use of the information. Excel is a great spreadsheet program, but does not lend itself to all the functionalities of a database program.
I would start with the employee numbers and names in a relatively unseen area of the spreadsheet. Create a list, say it starts in column K. There you list vertically the employee numbers of all who might submit a req. In column L, list all of the corresponding names. If you have more static information, make more columns in the same manner.
Then in, say, row one you have your headers. You could start your sheet by inputting whatever the new info is, for example, Column 1 is req #. If that’s what’s different for each row. You then enter the employee number and use the lookup command to look at the list to associate the employee number with whatever info you have in your list. Make sense?
You can’t really get the entire gist without seeing me waving my arms and explaining with my hands! Sorry.
Thank you all! The VLOOKUP function is working. This is really not anywhere near complex enough a listing to be using Access, though I do have it on my computer here at work. There are only about 150 caseworkers in the list (which I put on a separate worksheet in the workbook), though I do have to remember to be diligent about keeping the list up to date.
Again, thank you!
Database design 101 pointers:
If you don’t already, at some point you will have two caseworkers with the same or similar names. That, and you will have to be sure to correctly spell, capitalize, every name the same each time. (remember if it’s Shawn or Sean, John or Jean or Jon, or Denny or Dennis, etc.)If you instead enter the worker number, and have that look up the name and other stuff it may avoid problems, because in no case should worker numbers be duplicated, and having the name pop up automatically will make it easy to verify the name (vs the number popping up) Unfortunately, this may not be as user (you) friendly unless you somehow already have the worker number in front of you each time you enter the data. If it workable, it will make life easier in the long run.
Also, if you are using first and last names, (or initials) I suggest that you put them in seperate columns. This will allow you to more easilly sort your data into alphbetical order if you ever need to do that for a report, or to analyze caseload/worker etc.
Just came across this again and I noticed that scotandrsn gave instructions that I assume you are using.
Things to note:
It is not necessary to type in the $ signs to lock ranges just hit PF4 and it will do it for you.
In the vlookup add a third comma and after it enter false, otherwise you are doing a range lookup and will end up populating the field even if there is no matching value on your reference table.
This means that if you forget to update the reference table with a name vlookup will return #N/A to let you know.
You will no longer need to keep the reference table sorted as it will find the matching values. Do not sort lookup tables unless you intend to do range lookups.
Don’t worry about Capitalisation - none of the functions do.
If you wanted to get even fancier, when you enter the case workers name you can actually have someone able to select the the name from the list you created. In whichever cells you wish to enter the names go to the menu Data > Validation > Settings Tab. In the “Allow:” drop down select “List”. A new box “Source:” should appear. In that box you enter the range containing the list you created of the names to be used. and then click “OK”.
Now when someone clicks on the cell to enter a name they will get a dropdown and can select the name form the dorpdown. Or, if desired the name can still be typed in, but if it does not match any of the names on the list it will not be allowed.
One caveat, the list of names must be on the same worksheet as the cell you are going to be enetering the names. You cna just put it way off to the right, well out of sight. Or, what I prefer to do is put it in the first columns on th eleft and ten hide those columns.