| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need.
Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters A16 Carrots D16 3.75 A17 Oranges D17 7.95 A18 Onions D18 4.55 A19 Onions D19 4.55 A20 Potatoes D20 4.35 Thanks so much for your help ! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data entered from list automatically enters number in anothercell
Put this in D16:
=VLOOKUP(A16,A$2:B$6,2,0) then copy down. If you want it a bit more robust, then you will need something like this: =IF(A16="","",IF(ISNA(VLOOKUP(A16,A$2:B$6,2,0)),"" ,VLOOKUP(A16,A$2:B $6,2,0))) Hope this helps. Pete On Feb 19, 5:31*pm, gbryce wrote: I am using Excel 2007 *and here is an simplified example of what I need.. * * Items * * * * Price Potatoes * * * *4.35 Apples * * * * *5.55 Oranges * * * *7.95 Onions * * * * *4.55 Carrots * * * * 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: * * * * * * * *Excel Enters A16 Carrots * * * * * * * D16 *3.75 A17 Oranges * * * * * * *D17 *7.95 A18 Onions * * * * * * * *D18 *4.55 A19 Onions * * * * * * * *D19 *4.55 A20 Potatoes * * * * * * *D20 *4.35 Thanks so much for your help ! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data entered from list automatically enters number in another cell
Take a look VLOOKUP function in help menu
"gbryce" wrote: I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters A16 Carrots D16 3.75 A17 Oranges D17 7.95 A18 Onions D18 4.55 A19 Onions D19 4.55 A20 Potatoes D20 4.35 Thanks so much for your help ! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data entered from list automatically enters number in another
I had never used VLOOKUP function before but now I am beginning to understand
how it works. It is exactly what I was needing. Thanks Guys. "Pete_UK" wrote: Put this in D16: =VLOOKUP(A16,A$2:B$6,2,0) then copy down. If you want it a bit more robust, then you will need something like this: =IF(A16="","",IF(ISNA(VLOOKUP(A16,A$2:B$6,2,0)),"" ,VLOOKUP(A16,A$2:B $6,2,0))) Hope this helps. Pete On Feb 19, 5:31 pm, gbryce wrote: I am using Excel 2007 and here is an simplified example of what I need.. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters A16 Carrots D16 3.75 A17 Oranges D17 7.95 A18 Onions D18 4.55 A19 Onions D19 4.55 A20 Potatoes D20 4.35 Thanks so much for your help ! . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data entered from list automatically enters number in another
You're welcome - thanks for feeding back.
Pete On Feb 19, 10:40*pm, gbryce wrote: I had never used VLOOKUP function before but now I am beginning to understand how it works. It is exactly what I was needing. Thanks Guys. "Pete_UK" wrote: Put this in D16: =VLOOKUP(A16,A$2:B$6,2,0) then copy down. If you want it a bit more robust, then you will need something like this: =IF(A16="","",IF(ISNA(VLOOKUP(A16,A$2:B$6,2,0)),"" ,VLOOKUP(A16,A$2:B $6,2,0))) Hope this helps. Pete On Feb 19, 5:31 pm, gbryce wrote: I am using Excel 2007 *and here is an simplified example of what I need.. * * Items * * * * Price Potatoes * * * *4.35 Apples * * * * *5.55 Oranges * * * *7.95 Onions * * * * *4.55 Carrots * * * * 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: * * * * * * * *Excel Enters A16 Carrots * * * * * * * D16 *3.75 A17 Oranges * * * * * * *D17 *7.95 A18 Onions * * * * * * * *D18 *4.55 A19 Onions * * * * * * * *D19 *4.55 A20 Potatoes * * * * * * *D20 *4.35 Thanks so much for your help ! .- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change value of cell automatically if entered the wrong number | Excel Worksheet Functions | |||
| Selecting from drop down list enters multiple cell data | Excel Worksheet Functions | |||
| Mouse click automatically enters character into cell. | Excel Worksheet Functions | |||
| How do I get data automatically entered into a cell? | Excel Worksheet Functions | |||
| Master worksheet automatically enters data into sub worksheets | Excel Discussion (Misc queries) | |||
Linear Mode
