Auto Populate from 2 drop-down menus

[mag]

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!

 

[daniels012]

Are you using the drop down from FORMS TOOLBAR or CONTROL TOOLBAR?

Michael

 

[mag]

I created each drop-down menu using Date Validation.

 

[daniels012]

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]

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]

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]

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]

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

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]

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))