Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
|
|||
|
|||
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.
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array formula to show differences in 2 lists? | Excel Worksheet Functions | |||
Formula Help with Lists | Excel Worksheet Functions | |||
Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions | |||
Array Formula? Lists? | Excel Worksheet Functions | |||
Referencing lists in a nested IF formula | Excel Worksheet Functions |