DropDown list to return value from another sheet

roadrage

New Member
Joined
Jan 21, 2005
Messages
4
I have 2 sheets and am looking to use a drop down on sheet 2 "Quotes" Cell B11 to return a value from Sheet 1 "Rates" E6:E39 into Cell B13.

I have the dropdown list pulling from Sheet 1 A6:A39, I just can't get the values to line up and return in B13.

Any help would be greatly appreciated, I feel as though I have looked everywhere.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Have you tried VLOOKUP?

Here's a quick example:
Book2
ABCD
1Quotas$
2Bob$100
3Fred$150
4John$175
5
6Rates$
7John$175
8Fred$150
9Bob$100
Sheet1


Hope that helps,

Smitty
 

roadrage

New Member
Joined
Jan 21, 2005
Messages
4
Thanks for your response. I've looked into VLOOKUP and INDEX, MATCH but I can't get them to make sense and return the value I'm looking for.

One sheet is called Quotes and is various info:

A1 | B1 | C1 | D1

AA .01 .02 .04
BB .02 .04 .08
CC .03 .06 .12
DD .04 .08 .16

The other sheet has a drop down (in Cell B11) containing AA, BB, CC, DD. What I'm looking to do is have the values in Column D (E in my workbook) sync up with the drop down so when I select CC in the drop down, .12 is returned in Cell B13.

Is that either VLOOKUP or INDEX, MATCH???
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Based on your info, try:

=VLOOKUP(B11,Quotes!A1:D4,4,FALSE)

Do you always want to return from that last column? If that will be a variable, then this will need some adjusting.

Smitty

Note that if you look to the bottom of the page for the link to Colo's HTML Maker, you can post snapshots of your sheet like I did. (Beats the crap outta' typing it! :wink: )
 

roadrage

New Member
Joined
Jan 21, 2005
Messages
4

ADVERTISEMENT

Guess VLOOKUP worked, I had to rework it a bit, but it's correct:

=VLOOKUP(B11,Rates!A6:E39,5,FALSE)

Thanks for your help!!
 

Ron Morris

Active Member
Joined
Oct 18, 2004
Messages
430
Hi,

Is your dropdown Control from ActiveX Control or Forms Control Toolbar?

The forms control will return the row number of the value in the list, for example: AA would be 1.

The ActiveX Control will return the value in the dropdown box and so the Vlookup that Smitty gave you should work.

Ron
 

roadrage

New Member
Joined
Jan 21, 2005
Messages
4
My dropdown is a Data Validation List, dunno if that is ActiveX/Form Control.

I have two sheets, sheet 1 (Rates) has 9 or 10 columns containing country codes, country, and various rates. The rates I needed are in column E.

I created a Data Validation List of the country codes in column A of sheet 1 (Rates) on sheet 2 (Quotes) Cell B11. With the =VLOOKUP formula I am now able to select JPN in the dropdown and have B13 fill in with the rate from the corresponding row of sheet 1 (Rates).

Much more automated - thanks!!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,912
Messages
5,655,914
Members
418,251
Latest member
aondrla

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