Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Pick List associated cell
Can anybody help me with this issue. I am trying to select an item from a pick list and be able to automatically copy the adjacent cell value to another cell.
Example: This is the pick list PAPER HOLDER - LEVEL I (CHROME) $2.80 TOWEL BAR - 24" LEVEL I (CHROME) $2.80 TOWEL BAR - 18" LEVEL I (CHROME) $2.80 Once I choose the item to fill in another cell location, I want the adjacent price to automatically fill in another cell location as well. --- frmsrcurl: http://msgroups.net/microsoft.public.excel.misc/23 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Pick List associated cell
One idea, use a formula in a col adjacent to the DV pick list
Let's say your pick list as posted is in B2 down You could have this in say C2, copied down: =IF(B2="","",MID(B2,SEARCH("$",B2)+1,99)+0) Format col C as currency to taste The above relies on searching for the "$" in the pick list's string, then stripping all the numbers after the "$" using MID. The last "+0" bit is to coerce the resulting text numbers (as MID returns it as text) to real numbers. -- Max Singapore "Ty" / wrote in message ... Can anybody help me with this issue. I am trying to select an item from a pick list and be able to automatically copy the adjacent cell value to another cell. Example: This is the pick list PAPER HOLDER - LEVEL I (CHROME) $2.80 TOWEL BAR - 24" LEVEL I (CHROME) $2.80 TOWEL BAR - 18" LEVEL I (CHROME) $2.80 Once I choose the item to fill in another cell location, I want the adjacent price to automatically fill in another cell location as well. --- frmsrcurl: http://msgroups.net/microsoft.public.excel.misc/23 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Pick List associated cell
Create a new table (A1:B3, say) on a different sheet.
Give the first column (A1:A3) a nice name. Use Data|Validation to create a dropdown list with those options as the choice. Use a formula in the adjacent cell to retrieve the value in the adjacent column in the table. =if(a1="","",vlookup(a1,sheet2!a1:b3,2,false)) Debra Dalgleish has some notes about naming the range: http://contextures.com/xlDataVal01.html#Name And for using =vlookup(): http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://contextures.com/xlFunctions02.html#Trouble Ty wrote: Can anybody help me with this issue. I am trying to select an item from a pick list and be able to automatically copy the adjacent cell value to another cell. Example: This is the pick list PAPER HOLDER - LEVEL I (CHROME) $2.80 TOWEL BAR - 24" LEVEL I (CHROME) $2.80 TOWEL BAR - 18" LEVEL I (CHROME) $2.80 Once I choose the item to fill in another cell location, I want the adjacent price to automatically fill in another cell location as well. --- frmsrcurl: http://msgroups.net/microsoft.public.excel.misc/23 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I build a user pick a value from a list to insert into a cell | Excel Discussion (Misc queries) | |||
how do i get a drop down pick list in a cell | Excel Worksheet Functions | |||
how do i get a drop down pick list in a cell | Excel Worksheet Functions | |||
Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) | |||
Calculate Value of Cell From Pick List Choice | Excel Discussion (Misc queries) |