DropDown list to return value from another sheet

[roadrage]

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.

 

[Smitty]

Welcome to the Board!

Have you tried VLOOKUP?

Here's a quick example:

Book2
	A	B	C	D
1	Quotas	$
2	Bob	$100
3	Fred	$150
4	John	$175
5
6	Rates	$
7	John	$175
8	Fred	$150
9	Bob	$100
Sheet1

Hope that helps,

Smitty

 

[roadrage]

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]

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]

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]

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]

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