Auto Populate from 2 drop-down menus

mag

New Member
Joined
Oct 16, 2006
Messages
5
I am new to this website and I am not familiar with macros.

I have a drop-down menu in Column A (labor categories), and drop-down menu in Column B (Region).

There are 20 labor categories in the Column A drop-down. There are 3 regions in the Column B drop-down.

In Column F (labor rate) I would like Excel to auto-populate the rate based on the Labor Category selected and the Region where the work will be performed.

How do I do this?

Thanks for your help!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Are you using the drop down from FORMS TOOLBAR or CONTROL TOOLBAR?

Michael
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
OK Obiously you have a list somewhere, right?

Like:
Labor Category the Region labor rate
7 Southeast 1.7
8 West 1.3

add a new column (A) (you can even hide this column) that combines 7Southwest or 8West.

If this is close to what you have, without knowing more about the data you are using, you could use a vlookup formula.
=vlookup(Values in (A),Give the table to lookup, then the column of the results [my example would be column D])


Hope this helps,
Michael
 

mag

New Member
Joined
Oct 16, 2006
Messages
5

ADVERTISEMENT

Thank you for your help. It doesn't work, yet ... but I'm not giving up.

I have set up the following lists on a separate worksheet called "Lists"

- LaborCategory&RegionList
- LaborCategoryList
- RegionList

I also have a Rate Table Set Up with all possible combinations of Labor Category, Region, and Rate

When I run your suggested VLOOKUP, what cell should I be in?
 

mag

New Member
Joined
Oct 16, 2006
Messages
5
Are there any other ideas for how to do this?

The VLOOKUP didn't work because the Lookup Value will change depending on which combination of Category and Region is selected from each drop-down menu.

In other words, even after I created a new column with LaborCategoryRegion ... it doesn't lookup based on the values selected in the 2 drop-down menus.

Thanks,

Monica
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Hi, Monica,
WELCOME to the Board !
I also have a Rate Table Set Up with all possible combinations of Labor Category, Region, and Rate
can you show a little part of this Rate Table ?
if possible use a tool, else your table-layout will be messy
see my signature for "Table-It" or colo's HTMLmaker (bottom page, since your not familiar with macros, I warn you, this one is more complicated)

kind regards,
Erik
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
maybe this ?
   A      B       C    D  E      F       G    
 1 labor  region  rate    labors regions rate 
 2 labor3 region2 24      labor1 region1 5    
 3                        labor2 region1 10   
 4                        labor3 region1 20   
 5                        labor4 region1 25   
 6                        labor1 region2 8    
 7                        labor2 region2 16   
 8                        labor3 region2 24   
 9                        labor4 region2 32   
10                        labor1 region3 4    
11                        labor2 region3 9    
12                        labor3 region3 13   
13                        labor4 region3 18   

Blad6

[Table-It] version 06 by Erik Van Geit
Code:
RANGE   FORMULA (1st cell)
C2      {=INDEX(G2:G13,MATCH(A2&B2,E2:E13&F2:F13,0))}

{=formula}:
 enter formula without {}
 confirm with Control-Shift-Enter

[Table-It] version 06 by Erik Van Geit

first setup the same example
then go back to your project and adapt
 

mag

New Member
Joined
Oct 16, 2006
Messages
5
Thank you for your help. I really need to learn more Excel functions. This ended up working for this particular worksheet. I had a friend do it for me.


=IF(ISNA(VLOOKUP(A2&B2,RateTable!$A$2:$D$200,4,FALSE)),0,VLOOKUP(A2&B2,RateTable!$A$2:$D$200,4))
 

Watch MrExcel Video

Forum statistics

Threads
1,133,466
Messages
5,658,952
Members
418,478
Latest member
Adam19931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top