help with formula and lists
 

Hello

first of I will explain what i need. i work for a printing company and 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 per 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 example for laminating it can be: gloss, matt or un-laminated. Each costing a 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 white printed
15 sheets colour
Perfect bound


Now is it possible to have it so that if I select "gloss laminate" from a dropdown box it draws the figure from "cost per unit" in "labour" and 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 sheet "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 that will multiply the cost per sheet, by the number of sheets. And then go on to add all the other process costs, to ultimately end up with overall cost per unit.

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

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

Thanks in advance Leon

help with formula and lists
 

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!
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"leon barnes" wrote:


Hello

first of I will explain what i need. i work for a printing company and
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 per
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 example
for laminating it can be: gloss, matt or un-laminated. Each costing a
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 white
printed
15 sheets colour
Perfect bound


Now is it possible to have it so that if I select "gloss laminate" from
a dropdown box it draws the figure from "cost per unit" in "labour" and
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 sheet
"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 that
will multiply the cost per sheet, by the number of sheets. And then go
on to add all the other process costs, to ultimately end up with
overall cost per unit.

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

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

Thanks in advance Leon




--
leon barnes

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