Nigel,

The way to go about this is to put have your costs in a sheet, say
"Materials" in two columns

mdf £8.50
birch ply £14.50
small glass shelves £25.00

I'm sure I don't have the right costs with the materials, but you can take
care of that.

Now on the Material Data sheet, set up Date - Validation - List, and point
it to the first column of the Materials sheet. Since Data Validation can't
refer to cells outside the sheet, you'll have to assign a name, Material,
(Insert - Name - Define) to the first column of your Materials table. Then
select the cells in the second sheet, Data - Validation - List, and in the
Source box, put = Material (with the equals). Check the "Dropdown" box.
Now you can select the materials right in the Material Data sheet, with the
dropdown provided by Data Validaion. For the material costs, perhaps in the
next column of the Material Data sheet, use:

=VLOOKUP(A2,Materials!A2:B65000,2,FALSE)

This will look up the cost for each material in sheet Materials. You can
add a multiplier for quanties, if needed:

=VLOOKUP(A2,Materials!A2:B65000,2,FALSE) * B2

Where B2 is the quantity column.
--
Earl Kiosterud
www.smokeylake.com

"Nigel" <Nigel@discussions.microsoft.com> wrote in message
news:FAC2C197-79D4-476F-B713-389AA39E6C3A@microsoft.com...
> 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.