Drop Down Lists and Auto Fill
 

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?

Drop Down Lists and Auto Fill
 

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

On Mar 21, 3:52 pm, Ken wrote:
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?

Drop Down Lists and Auto Fill
 

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
"Pete_UK" wrote:

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

On Mar 21, 3:52 pm, Ken wrote:
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?

Drop Down Lists and Auto Fill
 

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

On Mar 21, 4:57 pm, Ken wrote:
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



"Pete_UK" wrote:
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

On Mar 21, 3:52 pm, Ken wrote:
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?- Hide quoted text -

- Show quoted text -

Drop Down Lists and Auto Fill
 

THANKS!! I really appreciate your help!!!

"Pete_UK" wrote:

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

On Mar 21, 4:57 pm, Ken wrote:
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



"Pete_UK" wrote:
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

On Mar 21, 3:52 pm, Ken wrote:
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?- Hide quoted text -

- Show quoted text -

Drop Down Lists and Auto Fill
 

You're welcome, Ken - thanks for feeding back.

Pete

On Mar 22, 1:55 pm, Ken wrote:
THANKS!! I really appreciate your help!!!



"Pete_UK" wrote:
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

On Mar 21, 4:57 pm, Ken wrote:
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

"Pete_UK" wrote:
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

On Mar 21, 3:52 pm, Ken wrote:
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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Drop Down Lists and Auto Fill
 

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



"Pete_UK" wrote:

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

On Mar 21, 3:52 pm, Ken wrote:
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?