Solved

Excel bill of materials costing sheet insert items from lookup tables

Posted on 2014-03-28
12
2,029 Views
Last Modified: 2014-03-31
Excel brains please help!

I'm trying to develop a "Costing Sheet" for totaling a bill of materials.
The sheet is easy for the user to build up a list of costs.  Each line has Drop Down Lists.
You can add new item rows on the sheet.


Eg.   If you were to design a t-shirt you would need a costing sheet like the following:

COSTING SHEET  (Bill of Materials)
Type of Garment ................................     {description}                       '   lookup Table 1
Fabric of Garment ..............................     {description}       {cost}     '   lookup Table 2
Embellishment on Garment ..............     {description}       {cost}     '   lookup Table 3
Job Template Cost ..............................     {description}       {cost}    '   calculation see {IF}
selection

Total __________________________________   $0.00
__________________________________________________

Lookup Table 1
Key ------  Code ------Description
1   --------  LS   -------Long Sleeve T Shirt        ' cost is determined on table 2 selection
2   --------  SS   -------Short Sleeve T Shirt       ' cost is determined on table 2 selection

Lookup Table 2
Key ------  Garment Code ------------ Description    ---------------- Cost
1 ------------  LS     ----------------------  Cotton           ---------------- $0.15
2 ------------- LS     ----------------------  Cotton Elastane -------------$0.20
3 ------------- SS     ----------------------  Cotton           ---------------- $0.11
4 ------------- SS     ----------------------  Cotton Elastane -------------$0.18

Lookup Table 3
Key ------- Description    -------------------- Cost
1 ----------  Embellishment A    -------------- $0.30
2 ----------  Embellishment B    -------------- $0.45
3 ----------  Embellishment C    -------------- $0.55
4 ----------  Embellishment D    -------------- {sub table}

Sub Table of Embellishment D
Key ------- Description    -------------------- Cost
1 ----------  1 Colour Print    --------------  $0.10
2 ----------  2 Colour Print     -------------- $0.20
3 ----------  3 Colour Print     -------------- $0.30
4 ----------  4 Colour Print     -------------- $0.40

Job Template Cost
IF LOOKUP TABLE 3 selection = 1 THEN  Job Cost = $0.50 ELSE
IF LOOKUP TABLE 3 selection = 2 THEN  Job Cost = $0.80 ELSE
IF LOOKUP TABLE 3 selection = 3 THEN  Job Cost = $0.90 ELSE
ELSE Job Cost = $0.00



It is like a database driven costing sheet  -  However it needs the flexibility to add new items.

This is example of a complete costing:
COSTING SHEET  (Bill of Materials)
Type of Garment ................................     [Long Sleeve T Shirt]
Fabric of Garment ..............................     [Cotton]                                                   $0.15
Embellishment on Garment ..............     [Embellishment D]   [2 Colour Print]       $0.20
Embellishment on Garment ..............     [Embellishment A]                                  $0.30
Embellishment on Garment ..............     [Embellishment B]                                  $0.45
Job Template Cost ..............................                                                                    $1.30

Total __________________________________   $2.40



I hope this explains what I'm looking for.   The most complicated part might be calcualting the 'Job Cost' line which I've used a IF formula, but it probably could be done better via a linked table or another method.

Open to all suggestions.

It could be done using a form with buttons?
It could be done using VB code?


I appreciate any advice.

Thanks
Mchalllinor
0
Comment
Question by:Mchallinor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39961403
Looks like you need the VLOOKUP function. The synatx for that is:

=VLOOKUP(LookupValue, LookUp Range, Offset, LookUp Type)

LookupValue - The value that you need to find in the left hand / first column of the data table
Lookup Range - The data table min which to find the value
Offset - The number of the column in the data range that containms the value that needs to be returned
LookUp type - True or False. False looks for an exact match for lookup value. True looks for the nearest match; for numbers it would be the nearest but not greater than, for text the nearest but not later in alphabet.

So, using your Embellishment D table as an example, with Table on tab called TableD:
Column A has index
Column B has description
Column C has value

For your example, you are trying to find "2 Colour Print" and return $0.20 so the formula would be, assuming "2 colour print" from dropdown in B3:

=VLOOKUP($B3,TableD!$B$1:$C$5,2,False)

If that doesn't help, a sample file would help.

Thanks
Rob H
0
 
LVL 9

Accepted Solution

by:
rfportilla earned 200 total points
ID: 39961455
This is getting dangerously close to needing a database.  At the very least, you will need to have someone maintain the formulas.  

I have done this in the past 2 different ways.

1. Using validation with Lists.   This will create dropdowns that you can link to a list.  Unless Microsoft has improved this, the list will have to be in the same spread sheet.  You can hide it somewhere on the sheet if you don't want it visible.  Additionally, to support the pricing rules, you will want to have intermediate calculation cells.  In other words, all of the if statements should be put into hidden cells so that the calculations are made step by step without being visible to the user.  I might even lock the worksheet after completing it to make sure the end user can't easily discover my secrets.

This would require a mid-level Excel person to make it work and look acceptable.  There would also be a bit of maintenance involved because of all of the formulas and validations that need to be maintained.  

2. I have done this before with macros.  This takes a while to write and needs a good macro person.  

This also requires that your user allows macros to run. This has been an issue at some client sites.  

Good luck. I hope this helps.
0
 

Author Comment

by:Mchallinor
ID: 39961488
Thanks both rfportilla and robhenson

This morning I decided to jump into it -  Yes I'm using Data validation lists + vLOOKUP to return values.
There are some complexities that I just need to get my head around.

I should have a sample file in another hour.  I'll post it on here and you can tell me if you would have done it the same as me or if you have any suggestions.

Thanks..   Mchallinor
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 9

Assisted Solution

by:Christopher Jay Wolff
Christopher Jay Wolff earned 175 total points
ID: 39961598
I think this is what you might be trying to do.  The attached file is made up numbers for ink costs and different t-shirt sizes to give you the idea.  Click on the cells in H to get drop downs.

Is this what you're looking for?
T-ShirtCostExcel2003.xls
T-ShirtCostExcel2013.xlsx
0
 

Author Comment

by:Mchallinor
ID: 39961624
Hi Chris,

Yes,  That's how I've done it so far, although my example I'm currently working on is a little more complex, but thanks for that example.

I think it's the cross relationships between the tables that I find most complex.

Wait for my example
0
 

Author Comment

by:Mchallinor
ID: 39961716
Hi,

OK, I've attached my progress so far.
I think it matches the suggestions so far, but I if you have any other tips , please let me know.

The difficulty I have at the moment, is although I can insert new lines the Colour Spin Button doesn't replicate.  I'm having a bit of trouble working out whats the best method of controlling the colour cost multiplier.

Thanks in advance for all the great help.

Mchallinor
Costing-Sheet--BOM--V1.xlsm
0
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 125 total points
ID: 39961823
What you really need here is a relational database. You can fell a tree with a penknife, but the proper tool for the job is either an axe or a chainsaw, sure you can accomplish what you want in Excel, but Excel isn't the proper tool for the job.
0
 

Author Comment

by:Mchallinor
ID: 39961845
MacroShadow,

Yes, I do agree with you.  So some back ground information to ease your concerns...
I've got to model this best I can in excel to give the information to my software team, they will build this all on a SQL database.

So never fear this is just a conceptual document so that the software guys can see how I expect it to work (but much better) using SQL.

Thanks
Mchallinor
0
 
LVL 9

Expert Comment

by:Christopher Jay Wolff
ID: 39961974
Maybe if it's conceptual, don't spend time on the VB, and just use validation lists to get it working.  You can make it look about the same as what you have in less time.  For dependent lists, use

 =INDIRECT(Cell)

for your list source, rather than a name.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39964281
Change the formula in G11 to
=IF(COUNTIF(D11,D11),IF(D11<>"Type D",H11,VLOOKUP(F11,Colour_Price_Books!$B$2:INDEX(Colour_Price_Books!$C$2:$C$1000,SUMPRODUCT(--(Colour_Price_Books!$C$2:$C$1000<>""))),2,FALSE)),"")

Open in new window

0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39964285
btw, what are you trying to accomplish with the Add Embellishment and Delete Row buttons?
0
 

Author Comment

by:Mchallinor
ID: 39966072
Dear all,

Thanks for all the suggestions and help.   I've developed this beyond recognition from that original sample I uploaded.    

I've achieved my objective and the concept is with the developers now.

Thanks for the comments.

Mchallinor
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question