Another Data Validation question

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.

You can use data validation, and vlookup formulas to do this. There are
instructions in Excel's Help, and here:

http://www.contextures.com/xlDataVal01.html

http://www.contextures.com/xlFunctions02.html

and instructions for an order form that uses this technique here:

http://www.contextures.com/xlOrderForm01.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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.

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