Debugging an "IF" formula

[DarkSmile]

Hallo,

I'm back with another question.. I will try my best to explain what I'm doing.

I got a file with multiple sheets, on sheet1 (called Food) I have a button with the following code:

Sub Alles_Aangeduid()
  Sheets("ACS-002").Range("A3:B15").Copy Destination:=Sheets("Food").Range("A13")
End Sub

This script copies specific cells from sheet2 (called ACS-002) to sheet one (Food)
It's also copying cells with formulas, but whenever I change value (run the formula) it gives me an error.. Can someone help me debug de error?

Here both formulas, B14 is on purpose not locked, because it's not the same cell in Sheet1 & 2.

=IF(B14="Ja",IF('ACS-002'!$B$28,'ACS-002'!$D$41,IF('ACS-002'!$B$29,'ACS-002'!$D$42,IF('ACS-002'!$B$30,'ACS-002'!$D$43,IF('ACS-002'!$B$31,'ACS-002'!$D$44,"ERROR")))))

=IF(B14="Nee",IF('ACS-002'!$B$28,'ACS-002'!$B$41,IF('ACS-002'!$B$29,'ACS-002'!$B$42,IF('ACS-002'!$B$30,'ACS-002'!$B$43,IF('ACS-002'!$B$31,'ACS-002'!$B$44,"ERROR")))))

When changing a value it gives me the "ERROR" (= FALSE) It shouldn't give error but a value.

TCA berekening.xlsm
	A	B	C
12
13	Parameters : ACS-002 Ind Lait
14	Klein bedrijf?
15	Aantal Producttypes:
16	Producttypes:
17	Productstromen naar de dierenvoeding?:
18
19	Aantal Producttypes:	0
20	Klein bedrijf	FALSE
21	Normaal bedrijf	FALSE
22	Dierenvoeders	0
23	Totaal klein	0
24	Totaal normaal	0
25	Totaal auditduur:	0
26
Food

Cell Formulas
Range	Formula
B19	B19	=B15
B20	B20	=IF(B14="Ja",IF('ACS-002'!$B$28,'ACS-002'!$D$41,IF('ACS-002'!$B$29,'ACS-002'!$D$42,IF('ACS-002'!$B$30,'ACS-002'!$D$43,IF('ACS-002'!$B$31,'ACS-002'!$D$44,"ERROR")))))
B21	B21	=IF(B14="Nee",IF('ACS-002'!$B$28,'ACS-002'!$B$41,IF('ACS-002'!$B$29,'ACS-002'!$B$42,IF('ACS-002'!$B$30,'ACS-002'!$B$43,IF('ACS-002'!$B$31,'ACS-002'!$B$44,"ERROR")))))
B22	B22	=IF(B17="Ja",'ACS-002'!$A$46,)
B23	B23	=IF(B20<>FALSE,B20+B22,0)
B24	B24	=IF(B21<>FALSE,B21+B22,0)
B25	B25	=SUM(B23:B24)

Cells with Data Validation
Cell	Allow	Criteria
B14	List	=KleinBedrijf
B15	List	=Aantal
B16	List	=Producttypes
B17	List	=Dierenvoeders

TCA berekening.xlsm
	A	B	C	D	E
1	Benodigde parameters
2
3	Parameters : ACS-002 Ind Lait
4	Klein bedrijf?
5	Aantal Producttypes:
6	Producttypes:
7	Productstromen naar de dierenvoeding?:
8
9	Aantal Producttypes:	0
10	Klein bedrijf	FALSE
11	Normaal bedrijf	FALSE
12	Dierenvoeders	0
13	Totaal klein	0
14	Totaal normaal	0
15	Totaal auditduur:	0
16
17
18
19
20
21	Dropdown list: Klein bedrijf	Dropdown list: Aantal	Dropdown list: Producttypes	Dropdown list: Dierenvoeders
22	Ja	1	UHT-consumptiemelkdranken en UHT- room	Ja
23	Nee	2	Gesteriliseerde consumptiemelkdranken en room	Nee
24		3	Gepasteuriseerde consumptiemelkdranken en room
25		4 of meer	Gefermenteerde melk
26			Thermisch behandelde gefermenteerde melk
27			Verse kaas
28	!=B9=B22	FALSE	Harde kaas op basis van gepasteuriseerde melk
29	!=B9=B23	FALSE	Mozzarella op basis van gepasteuriseerde melk
30	!=B9=B24	FALSE	Zachte kaas op basis van gepasteuriseerde melk
31	!=B9=B25	FALSE	Zure boter op basis van gepasteuriseerde melk, verse zure karnemelk, thermisch behandelde zure karnemelk
32			Melkpoeder
33			Neutrale desserts
34			Rauwmelkse kazen
35			Zure boter op basis van rauwe melk en verse zure karnemelk
36
37
38
39	Normaal		small enterprise
40	Aantal producttypes	Te besteden mandagen (uren)	Aantal producttypes	Te besteden mandagen (uren)
41	1	10	1	4
42	2	12	2	6
43	3	14	3	8
44	4 of meer	16	4 of meer	10
45	Dierenvoeders (uren)
46	2
47
ACS-002

Cell Formulas
Range	Formula
B9	B9	=B5
B10	B10	=IF(B4="Ja",IF('ACS-002'!$B$28,'ACS-002'!$D$41,IF('ACS-002'!$B$29,'ACS-002'!$D$42,IF('ACS-002'!$B$30,'ACS-002'!$D$43,IF('ACS-002'!$B$31,'ACS-002'!$D$44,"ERROR")))))
B11	B11	=IF(B4="Nee",IF('ACS-002'!$B$28,'ACS-002'!$B$41,IF('ACS-002'!$B$29,'ACS-002'!$B$42,IF('ACS-002'!$B$30,'ACS-002'!$B$43,IF('ACS-002'!$B$31,'ACS-002'!$B$44,"ERROR")))))
B12	B12	=IF(B7="Ja",'ACS-002'!$A$46,)
B13	B13	=IF(B10<>FALSE,B10+B12,0)
B14	B14	=IF(B11<>FALSE,B11+B12,0)
B15	B15	=SUM(B13:B14)
B28	B28	=B9=B22
B29	B29	=B9=B23
B30	B30	=B9=B24
B31	B31	=B9=B25

Named Ranges
Name	Refers To	Cells
Aantal	='ACS-002'!$B$22:$B$25	B28

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=KleinBedrijf
B5	List	=Aantal
B6	List	=Producttypes
B7	List	=Dierenvoeders

 

[mart37]

=IF(B14="Ja",IF('ACS-002'!$B$28,'ACS-002'!$D$41,IF('ACS-002'!$B$29,'ACS-002'!$D$42,IF('ACS-002'!$B$30,'ACS-002'!$D$43,IF('ACS-002'!$B$31,'ACS-002'!$D$44,"ERROR")))),"")

 

[DarkSmile]

=IF(B14="Ja",IF('ACS-002'!$B$28,'ACS-002'!$D$41,IF('ACS-002'!$B$29,'ACS-002'!$D$42,IF('ACS-002'!$B$30,'ACS-002'!$D$43,IF('ACS-002'!$B$31,'ACS-002'!$D$44,"ERROR")))),"")

Thank you!

 

[DarkSmile]

Thank you!

Edit: Sorry it doesn't work, it works in sheet ACS-002 but when copying over to sheet Food it breaks.. I will share the file it may be easier: TCA berekening.xlsm

 

[mart37]

What do you mean with breaks? The formulas are simular to ACS-002. When filled all the data (B16-B19) the formulas are working.

 

[DarkSmile]

See printscreens down beneath: when using your formula in sheet ACS-002 it gives this:

However, when using the copy button (to copy cells to sheet Food) it gives this

It's like the formula can't search for the needed parameters on the other sheet? Note: Sheet ACS-002 will be hidden so it needs to be able to calculate in sheet Food (ACS-002 is a placeholder)

 

[mart37]

Sorry I get no errors.

 

[mart37]

What wrong is. On ACS-002 are the cells B28-B31 dependent from cell B5.
When you change on FEED cell B17 then it would nothing change because B28-B31 are unchanged.

 

[mart37]

Change your formula in cell B10 for searching UREN with VLOOKUP(B9,'ACS-002'!$C$41:$D$44,2,FALSE)

 

[DarkSmile]

Change your formula in cell B10 for searching UREN with VLOOKUP(B9,'ACS-002'!$C$41:$D$44,2,FALSE)

This one works, it just needed a little tweaking. Changed it to

(first one B10, second one B11)

=IF(B4="Ja",VLOOKUP(B9,'ACS-002'!$C$41:$D$44,2,FALSE))
=IF(B4="Nee",VLOOKUP(B9,'ACS-002'!$A$41:$B$44,2,FALSE))