VLOOKUP creating spill error

[nicic]

Driving me mad. I had this working perfectly. Now for some reason, it's not
It either comes up with a spill error, or doesn't work at all. Not sure if I can share the workbook?
Honestly, I had this code sorted weeks ago, and it's been running fine.

I've checked previous versions and it's not working on there either... no idea what I have done...


=IF(ISNA(VLOOKUP(B10:B28,'Master Price List'!$B$7:$F$5000,5,FALSE)),"",VLOOKUP(B10:B28,'Master Price List'!$B$7:$F$5000,5,FALSE))

And then it worked again, and then stopped.... arrghhh...

 

[awoohaw]

Mr. Excel has a tool called xl2bb add in that allows posting of mini worksheets. (Link below).
In this circumstance it is really critical that you share the underlying range (a portion of it). as well as the location and surrounding cells of where you have the formula.

 

[nicic]

Soap Costings Spreadsheet v1a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2		Solid Lotion - Peppermint
3
4		How many units of product does your Batch make?	14				Total Cost per item (Ingredients, Packing and other costs)		£0.75
5
6
7		Recipe Cost (per Batch)					Packaging & Other Costs (per item)					RRP by Multiply
8		Ingredients	g in recipe	Cost per gram	Cost		Item	Qty	Cost per item	Cost		Multiply by	5
9												Suggested RRP	£3.76
10		Clay - Purple Brazilian	2	0.1186			15g tin	1	0.312	0.312		Gross Profit per bar	£3.01
11		Beeswax (SP)					15g Tin stickers (back)	1	0.11	0.11		Gross Profit per batch	£42.11
12		Beeswax (SP)					15g Tin stickers (front)	1	0.11	0.11		% Profit	80%
13							15g Tin stickers (front)	2	0.11	0.22
14							60g Tin		0.42			RRP by Profit Margin
15							15g Tin stickers (front)		0.11			% Profit	80%
16												Suggested RRP	£3.76
17												Gross profit per bar	£3.01
18												Gross profit per batch	£42.11
19												% Profit	80%
20
21												Profit by RRP
22												RRP	£4.50
23												Gross profit per bar	£3.75
24												Gross profit per batch	£52.47
25												% Profit	83%
26
27												Wholesale	50%
28
29												Wholesale Price
30		Batch Total					Packing Total					RRP	£5.00
31		Gross Total	units per batch									Wholesale Price	£2.50
32				Cost per unit			£0.7520					Gross Profit	£1.75
33		0	14	£0.0000								% Profit	70%
34
35
36
Solid Lotion - Peppermint

Cell Formulas
Range	Formula
B2	B2	=TEXTAFTER(CELL("filename",A1),"]")
I4	I4	=SUM(D33+G32)
D10	D10	=IF(ISNA(VLOOKUP(B10,'Master Price List'!B6:F33,5,FALSE)),"",VLOOKUP(B10,'Master Price List'!B6:F33,5,FALSE))
I10:I28	I10	=IF(ISNA(VLOOKUP(G10:G28,'Master Price List'!$H$5:$L$22,5,FALSE)),"",VLOOKUP(G10:G28,'Master Price List'!$H$5:$L$22,5,FALSE))
M9	M9	=M8*I4
M10	M10	=M9-I4
M11	M11	=SUM(M10*C4)
M12,M25,M19	M12	=M10/M9
J10:J13,J15:J28	J10	=IF(OR(H10="",I10=""),"",PRODUCT(H10,I10))
M16	M16	=I4/(1-M15)
M17	M17	=M16-I4
M18	M18	=SUM(M17*C4)
M23	M23	=M22-I4
M24	M24	=M23*C4
E18:E28	E18	=IF(OR(D18="",C18=""),"",PRODUCT(C18,D18))
G32	G32	=SUMIF(J10:J25,"<>#N/A")
B33	B33	=SUMIF(E10:E25,"<>#N/A")
C33	C33	=SUM(C4)
D33	D33	=SUM(B33/C33)
M31	M31	=M30*M27
M32	M32	=M31-I4
M33	M33	=M32/M31
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B34:B36	List	='Master Price List'!$G$6#
G10:G28	List	='Master Price List'!$G$6#
B10:B28	List	='Master Price List'!$A$6#

 

[nicic]

Soap Costings Spreadsheet v1a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
2
3
4		Ingredients						Packaging & Other
5		Ingredient	Supplier	Gram	Cost	Cost per Gram		Item	Supplier	Qty	Cost	unit cost
6	Almond Oil, Sweet	Almond Oil, Sweet	The Soapery	4600	40	0.008695652	15g tin	15g tin	Tinware	100	31.2	0.312
7	Beeswax (own)	Beeswax (own)	Home	1000	0	0	15g Tin stickers (back)	15g Tin stickers (back)	Evergreen goods store	1	0.11	0.11
8	Beeswax (SP)	Beeswax (SP)	The Soapery	1000	15.95	0.01595	15g Tin stickers (front)	15g Tin stickers (front)	Evergreen goods store	1	0.11	0.11
9	Castor Bean Oil	Castor Bean Oil	The Soapery	960	8.99	0.009364583	60g Tin	60g Tin	Tinware	100	42	0.42
10	Charcoal	Charcoal	The Soapery	100	4.65	0.0465	60g Tin Stickers (back)	60g Tin Stickers (back)	Evergreen goods store	11	0.11	0.01
11	Clay - Purple Brazilian	Clay - Purple Brazilian	The Soap Kitchen	100	11.86	0.1186	60g Tin Stickers (front)	60g Tin Stickers (front)	Evergreen goods store	8	0.11	0.01375
12	Cocoa Butter	Cocoa Butter	The Soapery	1000	12.95	0.01295	Belly Band (bar)	Belly Band (bar)	Evergreen goods store	6	0.08	0.013333333
13	Cocoa Powder	Cocoa Powder	Sainsburys	250	3.25	0.013	Hourly Rate (per min)	Soap Sticker (bar)	Evergreen goods store	12	0.11	0.009166667
14	coconut oil (76, refined)	coconut oil (76, refined)	The Soap Kitchen	5000	29.04	0.005808	Soap Sticker (bar)	Hourly Rate (per min)	n/a	60	15	0.25
15	EO Lemon	EO Lemon	Freshskin	90	7.9	0.087777778	0
16	EO Lemongrass	EO Lemongrass	Freshskin	89	7.95	0.089325843	0
17	EO Patchouli	EO Patchouli	Mystic Moments	500	40.46	0.08092	0
18	EO Peppermint	EO Peppermint	Freshskin	85	8.9	0.104705882	0
19	EO Rose Geranium	EO Rose Geranium	Freshskin	90	8.99	0.099888889	0
20	EO Rosemary	EO Rosemary	Freshskin	90	8.95	0.099444444	0
21	French Clay - Green	French Clay - Green	Mystic Moments	100	4.712	0.04712	0
22	French Clay - Pink	French Clay - Pink	Mystic Moments	100	4.712	0.04712	0
23	French Clay - Red	French Clay - Red	Mystic Moments	100	4.712	0.04712	0
24	French Clay - Yellow	French Clay - Yellow	Mystic Moments	100	4.712	0.04712	0
25	Gromwell Root	Gromwell Root	Mystic Moments	100	6.95	0.0695	0
26	Honey	Honey	Own	1	0	0	0
27	Honey (Sains)	Honey (Sains)	Sainsburys	340	1.29	0.003794118	0
28	Jojoba Oil	Jojoba Oil	The Soapery	868	30	0.034562212	0
29	Kaolin Clay	Kaolin Clay	The Soapery	1000	6.99	0.00699	0
30	Lavender EC	Lavender EC	Freshskin	90	9.75	0.108333333	0
31	Mango Butter	Mango Butter	The Soapery	5000	48.99	0.009798	0
32	Oats	Oats	sainsburys	1000	1	0.001	0
33	Olive oil	Olive oil	Sainsburys	1840	11	0.005978261	0
34	Sea salt	Sea salt	Sainsburys	350	1.2	0.003428571	0
35	Shea Butter (refined)	Shea Butter (refined)	The Soapery	5000	29.95	0.00599	0
36	Sodium Hydroxide	Sodium Hydroxide	The Soapery	5000	27.95	0.00559	0
37	Tumeric	Tumeric	Sainsburys	48	1.1	0.022916667	0
38	Vit E	Vit E	The Soapery	95	13.9	0.146315789	0
39	0						0
40	0						0
41	0						0
42	0						0
43	0						0
44	0						0
45	0						0
46	0						0
47	0						0
48	0						0
49	0						0
50	0						0
51	0						0
52	0						0
53	0						0
54	0						0
55	0						0
56	0						0
57	0						0
58	0						0
59	0						0
60	0						0
61	0						0
62	0						0
63	0						0
Master Price List

Cell Formulas
Range	Formula
A6:A5000	A6	=SORT('Master Price List'!$B$6:$B$5000)
G6:G5000	G6	=SORT('Master Price List'!$H$6:$H$5000)
L6:L14	L6	=SUM(K6/J6)
F6:F38	F6	=IFERROR(SUM(E6/D6),"")
Dynamic array formulas.

 

[nicic]

Just realised i'd been playing with code on first one but

Soap Costings Spreadsheet v1a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2		Solid Lotion - Peppermint
3
4		How many units of product does your Batch make?	14				Total Cost per item (Ingredients, Packing and other costs)		£0.75
5
6
7		Recipe Cost (per Batch)					Packaging & Other Costs (per item)					RRP by Multiply
8		Ingredients	g in recipe	Cost per gram	Cost		Item	Qty	Cost per item	Cost		Multiply by	5
9												Suggested RRP	£3.76
10		Clay - Purple Brazilian	2	0.1186			15g tin	1	0.312	0.312		Gross Profit per bar	£3.01
11		Beeswax (SP)					15g Tin stickers (back)	1	0.11	0.11		Gross Profit per batch	£42.11
12		Beeswax (SP)					15g Tin stickers (front)	1	0.11	0.11		% Profit	80%
13							15g Tin stickers (front)	2	0.11	0.22
14							60g Tin		0.42			RRP by Profit Margin
15							15g Tin stickers (front)		0.11			% Profit	80%
16												Suggested RRP	£3.76
17												Gross profit per bar	£3.01
18												Gross profit per batch	£42.11
19												% Profit	80%
20
21												Profit by RRP
22												RRP	£4.50
23												Gross profit per bar	£3.75
24												Gross profit per batch	£52.47
25												% Profit	83%
26
27												Wholesale	50%
28
29												Wholesale Price
30		Batch Total					Packing Total					RRP	£5.00
31		Gross Total	units per batch									Wholesale Price	£2.50
32				Cost per unit			£0.7520					Gross Profit	£1.75
33		0	14	£0.0000								% Profit	70%
34
Solid Lotion - Peppermint

Cell Formulas
Range	Formula
B2	B2	=TEXTAFTER(CELL("filename",A1),"]")
I4	I4	=SUM(D33+G32)
D10	D10	=IF(ISNA(VLOOKUP(B10,'Master Price List'!B6:F33,5,FALSE)),"",VLOOKUP(B10,'Master Price List'!B6:F33,5,FALSE))
I10:I28	I10	=IF(ISNA(VLOOKUP(G10:G28,'Master Price List'!$H$5:$L$22,5,FALSE)),"",VLOOKUP(G10:G28,'Master Price List'!$H$5:$L$22,5,FALSE))
M9	M9	=M8*I4
M10	M10	=M9-I4
M11	M11	=SUM(M10*C4)
M12,M25,M19	M12	=M10/M9
J10:J13,J15:J28	J10	=IF(OR(H10="",I10=""),"",PRODUCT(H10,I10))
M16	M16	=I4/(1-M15)
M17	M17	=M16-I4
M18	M18	=SUM(M17*C4)
M23	M23	=M22-I4
M24	M24	=M23*C4
E18:E28	E18	=IF(OR(D18="",C18=""),"",PRODUCT(C18,D18))
G32	G32	=SUMIF(J10:J25,"<>#N/A")
B33	B33	=SUMIF(E10:E25,"<>#N/A")
C33	C33	=SUM(C4)
D33	D33	=SUM(B33/C33)
M31	M31	=M30*M27
M32	M32	=M31-I4
M33	M33	=M32/M31
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B34:B42	List	='Master Price List'!$G$6#
G10:G28	List	='Master Price List'!$G$6#
B10:B28	List	='Master Price List'!$A$6#

 

[nicic]

Never mind - I've figured it... somehow automatic calculations was turned off in options!!!!! How I don't know!!!
That's 2 hours of my life I won't get back

 

[awoohaw]

I cannot replicate your error.
What cell did you have the formula in? Assuming you have that formula in cell D10 or I10 you need to be sure all cells D11:D28 and i11:i28 are empty.

I just saw your update. I'm happy you figured it out.

 

[nicic]

I cannot replicate your error.
What cell did you have the formula in? Assuming you have that formula in cell D10 or I10 you need to be sure all cells D11:D28 and i11:i28 are empty.

I just saw your update. I'm happy you figured it out.

Thanks so much for trying!
I've no idea why it did that, but it just did it again.... hmmmm.... shall investigate further. I've never changed those settings