help with formula and lists

[leon barnes]

Hello

first of I will explain what i need. i work for a printing company an
we need something that will calculate direct costs and overheads

i have created a workbook that contains the following:

sheet1 contains a list of 26 stocked papers, cost per/000 and cost pe
sheet.
sheet 2 contains labour costs for: binding, laminating, and printing
for each their is cost per hour, surplus materials, and cost per unit

Also for each of the processes their are 2-3 variations. for exampl
for laminating it can be: gloss, matt or un-laminated. Each costing
different amount.


Now this is where I am stuck

I want to be able to select say:
cover being 300silk, and gloss lamiated.
200 sheets black and whit
printed
15 sheets colour
Perfect bound


Now is it possible to have it so that if I select "gloss laminate" fro
a dropdown box it draws the figure from "cost per unit" in "labour" an
enters it in cell next to it.

And similar process with the "binding" as in if I selected "wiro bound
from the list it would draw the cost of "wiro binding" from the shee
"labour cost" and enter it in cell next to it.

And of course same with paper stock.

I am pretty sure once I got paper stock cost I can create a macro tha
will multiply the cost per sheet, by the number of sheets. And then g
on to add all the other process costs, to ultimately end up wit
overall cost per unit.

If any1 has any idea as to how I can create this list to link up wit
the costs, would be appreciated.

p.s if anyone would like me to send it to them so they can understan
what I am talking about a bit easier I would be more than happy to d
so, it contains no sensitive information, and is only 17.5kb in size

Thanks in advance Leo

 

[Luke M]

I'd look into the VLOOKUP function. Perfect for finding a value in a list,
and returning a corresponding value.

Now, if you're actually using a "combo box", you'll need to have the box
linked to a cell somewhere, and then use the INDEX function. If you're simply
using the drop down from a validation standpoint, you should have no problem.

the XL help file is pretty good at explaining both of these functions and
sounds like you have a pretty good idea of what you're doing already. Hope
that helps!

 

[leon barnes]

Thanks. Yes it was just a validation list i started with. and i wil
look into VLOOKUP and see how that works out for me.