Excel Drop Down Menu Work 1

[jasonmorovich]

I have an Excel sheet that I want to simplify for my users. I want to give them the ability to select data from a drop down and have the sheet prefill other columns based on their dropdown selection. I'm ok with the drop down part. I'm having trouble figuring out the next 2 steps. I have a sheet that has a workers title (HR Admin, accountant, I/T, etc). Each title has an associated code (ex 1234). So it's not possible to have the user memorize the code which is needed for billing purposes. They also have a billing rate associated with their title and code.
So I now have a sheet (used for the dropdown menu) with 3 columns (Title, Code, & Rate). I want the drop down list to show their title, however when they select that title the code associated with that title fills the column (not the title) and the rate tied to that code fills the adjacent column in the spreadsheet.
Am I asking too much of Excel? If not can someone help me figure out how to get this to happen?

Thanks in advance.

 

[AnotherHiggins]

How many titles are there? If there are only a few, you could use an IF statement in column B. If there are several, you could use a VLookUp.

Either way, you will use something like:
[tab]=If(A1="","",....)
to display an 'empty' cell if nothing has been selected yet.

Also: where are the options for the drop down being held? Did you enter the list in the Validation dialog box or are they stored somewhere in the workbook?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jasonmorovich]

Thanks for quick response. There are hundreds of titles that are going to be stored in a sperate worksheet in the same workbook using the Validation dialog box to access.

Thanks...

 

[AnotherHiggins]

Here's what I'd do:

In case you are not yet aware, you'll have to use a Named Range to reference a list stored on a different worksheet. I'd suggest using a Dynamic Named Range for this. That way you can easily add/delete job titles without having to make any other changes. You can search for Dynamic Ranges here on Tek-Tips and post if you have any questions. It might be easier to keep things straight if you create a second thread for that, if needed.

Let's say your list is on a sheet named Data in a range in column A. In columns B and C on that same sheet, go ahead and list the Code and Rate. Create another Named Range for all three columns. Let's call it rngMyLookupList.

Now back on the main sheet you will create the validation in, say, column A. In column B on the main sheet, you could enter the following (I'm using B2 assuming that Row1 contains headers):
[tab]=if(A2="","",VLookup($A2, rngMyLookupList, 2, 0)

You can drag that formula down a several rows. Depending on how it will be used, you might be able to get the formula to auto-populate in new rows as they're used by going to Tools > Options > Edit and ensure that Extend data range formats and formulas is checked. See Excel's help for more info on that option.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jasonmorovich]

That did exactly what I was looking for!!

Thanks again for the quick response and help.

 

[AnotherHiggins]

Glad to help


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.