Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Fill from Drop Down list | Excel Worksheet Functions | |||
Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions | |||
HOW DO I SET UP DROP DOWN CELLS TO AUTO FILL AS I START TYPING IN. | Excel Discussion (Misc queries) | |||
Auto Fill Cells, When Choosing From Drop-Down List... | Excel Worksheet Functions | |||
Creating an auto fill or drop down | Excel Discussion (Misc queries) |