BIG Sum Poblems!!

[OLLY-7]

Hi Guys,

I had some great help yesterday on a problem and hoping for some further help here.

I am a fledgling excel user but trying to make, what to me at least, seems like a complicated spread sheet.
The purpose of the spread sheet is to generate a quotation for a range of furniture and then disseminate this down through separate worksheets for breakdowns to production – i.e. upholstery, metal work, woodwork ect ect.

I have set up my first sheet which is the quotation with drop downs for qty, item code (that has a vlookup for filing in generic data such as size, drawing no. ect), drop downs for finishes and a drop down for fabric that in the next cell gives the correct fabric meterage for the right item code (this is what I got help on yesterday).

My big problems!!:

1) On the quotation sheet something isn’t quite working with my vlookups when selecting from the item code drop down. If you’re filling each row in turn as you go down then fine but if you miss a row or go back and change an item code to a new item code the vlookup no longer works. Any ideas?

2) Once that problem is fixed what I really want to do is on the ‘JOB – OVERVIEW BREAKDOWN’ sheet I would like to fill the ‘ORDER QTY’ column with the right qty from the Quotation sheet. I.e. show how many CH-10 chairs there are (there may be more than one line item for a CH-10 chair for example depending on qty and fabric spec). I have tried with sumproduct and sumif but no luck yet.

3) Similarly on the ‘JOB – UPHOLSTERY’ sheet I would like to fill in the total qty for all CH-10 chairs - for example (this will be using the same technique to solve problem 2 I imagine) but then I would also like to fill in the correct number of CH-10 chairs by fabric in the fabric breakdown. Again all referencing from the quotation sheet.

Hopefully I will be able to use the principle of the above solutions to continue to filter figures through the other production breakdown sheets. Such as…

4) Show on the ‘JOB – FABRIC’ sheet the total, for example, meterage of Fabric 1 for all items using Fabric 1.
Final big problem:

5) This may not be worth attempting but I’ll put it down here anyway… Say I have a quote with just 2 no. Ch-10 chairs and 1 no. SO-10 sofa. The production qtys are filtered through now across the various production breakdown sheets but this leaves me with many surplus to requirement entries for items not on this order. Is there a way of generating fresh sheets or removing the empty lines so only the CH-10 and SO-10 relevant entries are shown on each sheet. Sounds like a bigy to me so could just delete everything that is not needed (hopefully this won’t screw up formulas) but if something more automated can be done I’d be keen to learn!

Hope that all makes sense. Sorry to come to the forum with all these problems. I may be biting off more than I can chew but at least it’s a good way to learn!

Many thanks and any help will be amazing.

Regards

(P.S. basic file attached)


Sum Problems.xlsx

[etaf]

i will edit this post as I go through any points and add

point 1) you need to fix the range using $ - as you copy down you are changing the lookup range
VLOOKUP(E12,'Basic Lists'!A2:F21,2,FALSE)
should be
,VLOOKUP(E12,'Basic Lists'!$A$2:$F$21,2,FALSE)

point 2)
=SUMIF('MASTER QUOTATION'!E12:E35,B9,'MASTER QUOTATION'!D12:D31)
should work

[Harribone]

The answer to question 1):

You vlookup formula is OK in F12 for the description but you need to change the lookup table range to an ABSOLUTE reference:

=IF(ISNA(VLOOKUP(E12,'Basic Lists'!A2:F21,2,FALSE)),"",VLOOKUP(E12,'Basic Lists'!A2:F21,2,FALSE)) should be
=IF(ISNA(VLOOKUP(E12,'Basic Lists'!$A$2:$F$21,2,FALSE)),"",VLOOKUP(E12,'Basic Lists'!$A$2:F$21,2,FALSE))

Then drag this down, the dollar signs make sure that the reference stays the same (if you drag down to the next row A2 becomes A3 but $A$2 stays as it is, $A$2).
This will stope the vlookup showing blanks when you choose an item. Style, Size and Drawing lookup formulas need the $ applying in the same way.

[Harribone]

Number 3)
use SUMIFS in with this format:

=SUMIFS('MASTER QUOTATION'!D:D,'MASTER QUOTATION'!E:E,$C$16,'MASTER QUOTATION'!L:L,G17)

$C$16 is the item code on the same sheet and G17 is the fabric. (example formula is for H17)

[FDibbins]

Hi and welcome to the (addictive) world of excel.

That is quite a mouthful you are asking there, so lets take it 1 step at a time (its good to get all the questions out there though, in case some are related)

Q1. The reason your formulas are not working, is because you did not absolute the vlookup() range - it adjusted itself every time you copied it down.. Also, you are using 2010, so you can use iferror(). I modified your vlookup to an index/match, you can just copy this as is, down and across to where you need it

=IFERROR(INDEX('Basic Lists'!$A$1:$F$21,MATCH('MASTER QUOTATION'!$E12,'Basic Lists'!$A$1:$A$21,0),MATCH('MASTER QUOTATION'!F$11,'Basic Lists'!$A$1:$F$1,0)),"")

[Harribone]

Number 4)

JOB-FABIC sheet cell E16:

=SUMIF('MASTER QUOTATION'!L:L,'JOB - FABRIC'!A16,'MASTER QUOTATION'!N:N)

Drag down. I assume you are summing Column N in the Master sheet for certain fabrics. If not change N:N to the correct column.

[OLLY-7]

Thanks guys,

I'm going through your solutions as you write them. Give me a mo. Will come back shortly with my progress / questions!

Thank you very much.

Keep them coming!

[OLLY-7]

FDibbins: Thanks for your response.

Question: What is the advantage to using iferror() with index/match as oposed to my vlookups?

Thanks.


(Putting the $ signs in now to my vlookups as a starter - thanks Harribone / etaf)

[Harribone]

Number 5 can be done but it will take some time modifying your sheet to do this (unless another member has a quick way of seting this up).

On each JOB sheet you have listed every item possible and then some details relating to that item. As long as each item is set up to the same layout and the item specifics are stored on another sheet it can be modified to show only items selected.

Example - JOB overview Breakdown.

You need 1 Upholstery row for an item and 7 Metal rows for an item (at max.). The way I can set this up for you will mean each item if shown will have 1 Uphols/7 Metal rows per item even if not all needed. If this is OK with you I can work through your file to get all information in the right place then set up 'templates' for each JOB which will populate with active selections.

Let me know if this is acceptable ad I will crack on with it, chances are someone out there smarter than me (i.e. everyone else!) will come up with a solution in 2 secs in the meantime lol

[Harribone]

IFERROR as mentioned can be used with anything (vlookups and match), this will make your formulas much cleaner.

so for example F12 in Master Sheet is currently:
=IF(ISNA(VLOOKUP(E12,'Basic Lists'!$A$2:$F$21,2,FALSE)),"",VLOOKUP(E12,'Basic Lists'!$A$2:$F$21,2,FALSE))
with IFERROR:
=IFERRORVLOOKUP(E12,'Basic Lists'!$A$2:$F$21,2,FALSE)),"")
They both do the same but as you can see method 2 is clearer to read.

[OLLY-7]

Point 2 fix works great. Thanks etaf.

[FDibbins]

Also, I prefer to use index/match, it is far more flexible than just vlookup, also, it can "look behind" itself (ie, it can reference columns to the left of teh search column), something vlookup cant do.

And I know you can add match() to a vlookup to replace the "column number", but index/match/match is just a habit for me now I guess

[OLLY-7]

Thanks. Works a treat.

Number 3)
use SUMIFS in with this format:

=SUMIFS('MASTER QUOTATION'!D:D,'MASTER QUOTATION'!E:E,$C$16,'MASTER QUOTATION'!L:L,G17)

$C$16 is the item code on the same sheet and G17 is the fabric. (example formula is for H17)

[Tank997]

Try this, I don't really understand your #5 ? good luck!

[OLLY-7]

Got it. Thanks.

IFERROR as mentioned can be used with anything (vlookups and match), this will make your formulas much cleaner.

so for example F12 in Master Sheet is currently:
=IF(ISNA(VLOOKUP(E12,'Basic Lists'!$A$2:$F$21,2,FALSE)),"",VLOOKUP(E12,'Basic Lists'!$A$2:$F$21,2,FALSE))
with IFERROR:
=IFERRORVLOOKUP(E12,'Basic Lists'!$A$2:$F$21,2,FALSE)),"")
They both do the same but as you can see method 2 is clearer to read.

[OLLY-7]

Thank FDibbins.

Am I right in saying that to use this =FERROR formula when I copy it to, say, the 'style' cell I just change the F11 TO G11 and so on for 'drawing no.' ect?

Thanks.

Hi and welcome to the (addictive) world of excel.

That is quite a mouthful you are asking there, so lets take it 1 step at a time (its good to get all the questions out there though, in case some are related)

Q1. The reason your formulas are not working, is because you did not absolute the vlookup() range - it adjusted itself every time you copied it down.. Also, you are using 2010, so you can use iferror(). I modified your vlookup to an index/match, you can just copy this as is, down and across to where you need it

=IFERROR(INDEX('Basic Lists'!$A$1:$F$21,MATCH('MASTER QUOTATION'!$E12,'Basic Lists'!$A$1:$A$21,0),MATCH('MASTER QUOTATION'!F$11,'Basic Lists'!$A$1:$F$1,0)),"")

[FDibbins]

Its not the iferror() part that is doing the extracting, its this part...
=IFERROR(INDEX('Basic Lists'!$A$1:$F$21,MATCH('MASTER QUOTATION'!$E12,'Basic Lists'!$A$1:$A$21,0),MATCH('MASTER QUOTATION'!F$11,'Basic Lists'!$A$1:$F$1,0)),"")

And the way I have structured the formula using $, you cont need to change a thing copying it down and/or across - it auto adjusts.

$ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors...

$A$1 absolutes the entire cell reference, it will not chance when copied down or across
$A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across

All the iferr90 does is take care of times when an error message would be returned (nothing to find {empty row}, cant find an item etc)

[Harribone]

To further help explain the IFERROR:

=IFERROR(formula,"")

where formula is your formula. If your formula results in an error the IFERROR will jump to the "" and display this.

"" will show a blank cell
0 will show the number zero (can use any number if wanted)
"Not Found" will show Not Found, again you can use any text relevant to the situation
or you can use another formula (but if this formula result in an error the error will be displayed, you have to wrap a new iferror arounf this formula).

[OLLY-7]

Thanks very much.

Try this, I don't really understand your #5 ? good luck!

[OLLY-7]

Thanks FDibbins,

I'm just about following the sequence of the formula (understant how the IFERROR bit is working). I understand where each part of the main formula is referencing but dont fully understand the 'story' it is telling. Is it important that headings are the same for this to work? Also, question: what does a comma (,) mean in a formula?

Sorry if this is pretty basic stuff. I'm learning fast through!

Thanks.

Its not the iferror() part that is doing the extracting, its this part...
=IFERROR(INDEX('Basic Lists'!$A$1:$F$21,MATCH('MASTER QUOTATION'!$E12,'Basic Lists'!$A$1:$A$21,0),MATCH('MASTER QUOTATION'!F$11,'Basic Lists'!$A$1:$F$1,0)),"")

And the way I have structured the formula using $, you cont need to change a thing copying it down and/or across - it auto adjusts.

$ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors...

$A$1 absolutes the entire cell reference, it will not chance when copied down or across
$A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across

All the iferr90 does is take care of times when an error message would be returned (nothing to find {empty row}, cant find an item etc)

[OLLY-7]

Thanks. Understood.

To further help explain the IFERROR:

=IFERROR(formula,"")

where formula is your formula. If your formula results in an error the IFERROR will jump to the "" and display this.

"" will show a blank cell
0 will show the number zero (can use any number if wanted)
"Not Found" will show Not Found, again you can use any text relevant to the situation
or you can use another formula (but if this formula result in an error the error will be displayed, you have to wrap a new iferror arounf this formula).

[FDibbins]

in the formula...
=IFERROR(INDEX('Basic Lists'!$A$1:$F$21,MATCH('MASTER QUOTATION'!$E12,'Basic Lists'!$A$1:$A$21,0),MATCH('MASTER QUOTATION'!F$11,'Basic Lists'!$A$1:$F$1,0)),"")

often, to understand how a complex formula works, its best to start from inside and work outwards. so...

MATCH('MASTER QUOTATION'!$E12,'Basic Lists'!$A$1:$A$21,0)
Match will return a row or column number of what it is searching for
find the contents of 'MASTER QUOTATION'!$E12 in the ROW range 'Basic Lists'!$A$1:$A$21 the ",0" is for an exact match -this will returm how many rows down - from A1 -the match is

MATCH('MASTER QUOTATION'!F$11,'Basic Lists'!$A$1:$F$1,0)
find the contents of 'MASTER QUOTATION'!$F11 in the COLUMN range 'Basic Lists'!$A$1:$F$1 the ",0" is for an exact match -this will returm how many columns across - from A1 -the match is

So, with those 2, we now have a cross-reference of row number and column number to the value we are looking for. All that remains if to hook them together...this is where index() comes in.

=index(range, row, column)

then, to finish it all off, we use iferror() for error trapping and handling

Hope that helps?

[OLLY-7]

Thanks FDibbins!

That does actually make sense!

And you profer to use the Index/match combo rather than Vlookup as the one formula will adapt as copied accros (depending on how the $ are located) where as vlookup has to be tailored to each cell?

Thanks again for the explanation.

in the formula...
=IFERROR(INDEX('Basic Lists'!$A$1:$F$21,MATCH('MASTER QUOTATION'!$E12,'Basic Lists'!$A$1:$A$21,0),MATCH('MASTER QUOTATION'!F$11,'Basic Lists'!$A$1:$F$1,0)),"")

often, to understand how a complex formula works, its best to start from inside and work outwards. so...

MATCH('MASTER QUOTATION'!$E12,'Basic Lists'!$A$1:$A$21,0)
Match will return a row or column number of what it is searching for
find the contents of 'MASTER QUOTATION'!$E12 in the ROW range 'Basic Lists'!$A$1:$A$21 the ",0" is for an exact match -this will returm how many rows down - from A1 -the match is

MATCH('MASTER QUOTATION'!F$11,'Basic Lists'!$A$1:$F$1,0)
find the contents of 'MASTER QUOTATION'!$F11 in the COLUMN range 'Basic Lists'!$A$1:$F$1 the ",0" is for an exact match -this will returm how many columns across - from A1 -the match is

So, with those 2, we now have a cross-reference of row number and column number to the value we are looking for. All that remains if to hook them together...this is where index() comes in.

=index(range, row, column)

then, to finish it all off, we use iferror() for error trapping and handling

Hope that helps?

[OLLY-7]

Thanks FDibbins!

That does actually make sense!

And you profer to use the Index/match combo rather than Vlookup as the one formula will adapt as copied accros (depending on how the $ are located) where as vlookup has to be tailored to each cell?

Thanks again for the explanation.

in the formula...
=IFERROR(INDEX('Basic Lists'!$A$1:$F$21,MATCH('MASTER QUOTATION'!$E12,'Basic Lists'!$A$1:$A$21,0),MATCH('MASTER QUOTATION'!F$11,'Basic Lists'!$A$1:$F$1,0)),"")

often, to understand how a complex formula works, its best to start from inside and work outwards. so...

MATCH('MASTER QUOTATION'!$E12,'Basic Lists'!$A$1:$A$21,0)
Match will return a row or column number of what it is searching for
find the contents of 'MASTER QUOTATION'!$E12 in the ROW range 'Basic Lists'!$A$1:$A$21 the ",0" is for an exact match -this will returm how many rows down - from A1 -the match is

MATCH('MASTER QUOTATION'!F$11,'Basic Lists'!$A$1:$F$1,0)
find the contents of 'MASTER QUOTATION'!$F11 in the COLUMN range 'Basic Lists'!$A$1:$F$1 the ",0" is for an exact match -this will returm how many columns across - from A1 -the match is

So, with those 2, we now have a cross-reference of row number and column number to the value we are looking for. All that remains if to hook them together...this is where index() comes in.

=index(range, row, column)

then, to finish it all off, we use iferror() for error trapping and handling

Hope that helps?

[Harribone]

It's not the tailoring to each cell which FDibb is on about. To explain lets apply the two methods to the below situation:

Sheet1
COL A - Quantity
COL B - Item Code
COL C - Description
COL D - Item Colour (notice that this is spelt correctly with a 'u' lol)

Now say on sheet2 you want to enter in an item code (in A1) and return the desciption.
Vlookup: =VLOOKUP(A1,Sheet1!B:C,2,0)
Index/Match =INDEX(Sheet1!C:C,MATCH(A1,Sheet1!B:B,0))
These both work as well as each other.

Now say you want to enter the item as before in A1 but return the Quantity.
Vlookup: ------------
Index/Match: =INDEX(Sheet1!C:C,MATCH(A1,Sheet1!B:B,0))
You can't do this with a vlookup because the lookup value (item code) must be the first column. Then you can return a value as long as it is in a column AFTER the lookup column.
Index/Match is much more flexible because it doesn't care about first column etc. You tell it where to look and it obeys.

Hope this helps.

[FDibbins]

Harribone's last comment is very valid - vlookup() cannot look "behind" itself, that is, it cannot be used to look for anything to the left of what you are trying to find.

Added to that, I prefer the match() function, because you dont need to count how many columns over, you need to use - as long as the heading is identical to what you use for the search criteria, match() will find it no matter where it is

For me, I guess it comes down to...if i need to do a quick/simple find of something in a small-ish table (say in the 1st-3rd column), I will use vlookup. If i have a large table, and will be trying to find a bunch if different columns scattered through-out a big table, then index/match is my tool of choice

[OLLY-7]

Hi Guys,

Just a quick question:

I am trying to add up the total number of multiple criteria but is driving me mad as can't get the SUMIF or SUMIFS to work!

To be more precise I'm trying to show on my 'JOB - TURNING' sheet the total number of legs for chairs, ottomans and sofas (i.e. codes starting CH, OT, or SO).

Can someone put me out of my missery? Sure I'm just missing somehting really simple.

Thanks very again.

[etaf]

what have you used so far - can you give an example

=SUMIF('MASTER QUOTATION'!E:E,"CH*",'MASTER QUOTATION'!D:D)

[OLLY-7]

Think I have susssed it!

=SUM(SUMIFS('MASTER QUOTATION'!D12:D45,'MASTER QUOTATION'!E12:E45,{"CH - 10","CH - 10 B","CH - 11","OT - 09","OT - 09 B","OT - 10","OT - 11","OT - 12","SO - 10","SO - 10 B"}))

Thanks.

[etaf]

excellent , remember you can use wild cards *

if you wanted just CH OT and SO
=SUM(SUMIFS('MASTER QUOTATION'!D12:D45,'MASTER QUOTATION'!E12:E45,{"CH*","OT*","SO *"}))

that gives 105 - where as you get 81 from your formula - so I guess you dont want all CH, OT SO

[OLLY-7]

Thanks for that tip on the '*'. Good to know.

(Yes the OT-08 has different legs!)

Thank you.