Put the material in column A of sheet2 and the cost in column B.

Then you could use a formula like:

=if(a1="","",vlookup(a1,sheet2!a:b,2,false))

to return the cost (if the data|validation cell were A1).

Debra Dalgleish has lots of tips about =vlookup() at:
http://contextures.com/xlFunctions02.html

Nigel wrote:

Hi,
i have a question if anyone can help me with using data validation. i have a
column range ( C6:C30) i want to set it up so it gives me a drop down list
with the following content
mdf
birch ply
small glass shelves
large glass shelves
opal acrylic
clear acrylic
metal frames
ali channel

in the next column (e6:e25)

the costs
£8.50
£14.50
£25.00
£40.00
£33.00
£33.00
£65.00
£22.00

basically, i want to be able to select a material, and the cost alters
accordingly in the next column. i would like the information to be added on a
separate sheet called material data that i can update.

if anyone can help, it would be a great help.

thanks,

n.s.

--

Dave Peterson