Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Smile 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,722
Default 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   Report Post  
Junior Member
 
Posts: 2
Smile

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:
Originally Posted by Luke M View Post
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
array formula to show differences in 2 lists? goofy11 Excel Worksheet Functions 1 March 5th 08 06:54 PM
Formula Help with Lists TN.Jim Excel Worksheet Functions 5 May 4th 07 09:22 AM
Multiple lists with repeated values for dependet drop down lists mcmanusb Excel Worksheet Functions 1 September 29th 06 12:13 AM
Array Formula? Lists? FB Excel Worksheet Functions 2 August 14th 05 01:24 AM
Referencing lists in a nested IF formula JTinAtlanta Excel Worksheet Functions 1 July 30th 05 12:00 AM


All times are GMT +1. The time now is 02:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"