Drop Down Lists and Auto Fill

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a bid calculater. I have created a dropdown list of
equipment. Each piece of equipment has a rental rate and an operator rate.
How can I make it so when I select a piece of equipment from my drop down
list the rental rate is pulld too?
 
You would normally use a VLOOKUP for this, something like:

=VLOOKUP(A1,table,2,0)

assuming A1 is where your drop-down is, "table" is a named range
covering your data (which may be in another sheet), and that the rate
you want is the second column of "table".

Hope this helps.

Pete
 
Pete_UK, I've gotten confused. I dont have the spreadsheet vocabulary to
clearly express what my question is, so please bear with me.
I created a Table on Sheet 1. It has 4 columns. On Sheet 2 I created a drop
down list. The drop down list comes from Column A. On Sheet 2 when I select
an item from the drop down list, I want the information from columns b,c,and
d to fill next to the Item in the drop down list. Example:
SHEET 1
A B C D
Truck 40 40 120
SHEET 2

Dropdown fill fill fill
Truck 40 40 120
 
Highlight all the data in your table on Sheet1 (eg A1:D100 if you have
100 items) then Insert | Name | Define and give this range the name
"table" (without the quotes). Then on sheet2 you can have these
formulae to retrieve the items matching the pull-down in A1:

B1: =VLOOKUP(A$1,table,2,0)
C1: =VLOOKUP(A$1,table,3,0)
D1: =VLOOKUP(A$1,table,4,0)

Note that the only difference is the third parameter, which determines
which column of the table of data that you want the corresponding item
to be returned from. Once you have typed the formula into B1, you can
copy it to C1 and D1 and then just edit the formula to change the 2 to
a 3 or a 4 in turn.

Select different names in A1 and see the values change automatically.

Hope this helps.

Pete
 
THANKS!! I really appreciate your help!!!

Pete_UK said:
Highlight all the data in your table on Sheet1 (eg A1:D100 if you have
100 items) then Insert | Name | Define and give this range the name
"table" (without the quotes). Then on sheet2 you can have these
formulae to retrieve the items matching the pull-down in A1:

B1: =VLOOKUP(A$1,table,2,0)
C1: =VLOOKUP(A$1,table,3,0)
D1: =VLOOKUP(A$1,table,4,0)

Note that the only difference is the third parameter, which determines
which column of the table of data that you want the corresponding item
to be returned from. Once you have typed the formula into B1, you can
copy it to C1 and D1 and then just edit the formula to change the 2 to
a 3 or a 4 in turn.

Select different names in A1 and see the values change automatically.

Hope this helps.

Pete
Click to expand...
 
Pete,
You are the "hero of the day". I've been trying to solve that issue for
months and your solution worked like a charm and took all of 1 minute to
execute. Thank you for your expertise.

-NB
 
Back
Top