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

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

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