Hello,
I was wondering if anyone else does this, for my job I created a file to do some stuff. This file is always a work in progress, so it changes constantly, of course to not lose/break it all I keep backups before doing anything.
But instead of overwriting the previous backup I make a new one, the file has been in progress since +1y now, and I'm pretty proud to see how I'm evolving in Excel.
Anyone else doing this?
How it started:
How it's now + vba code + buttons that aren't showing beneath
I was wondering if anyone else does this, for my job I created a file to do some stuff. This file is always a work in progress, so it changes constantly, of course to not lose/break it all I keep backups before doing anything.
But instead of overwriting the previous backup I make a new one, the file has been in progress since +1y now, and I'm pretty proud to see how I'm evolving in Excel.
Anyone else doing this?
How it started:
calcul food facturation.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Naam Auditor | |||||
2 | Datum Audit | |||||
3 | Offer nummer | |||||
4 | ||||||
5 | Audit : | 0 | ||||
6 | Certificatie kosten Norm 1 - Prijzen 2019 : | |||||
7 | Verplaatsing-déplacement : | Totaal audit uur | ||||
8 | nazicht actieplan -verif plan action : | Totaal raportage tijd | ||||
9 | registratie kosten Norm 1 - frais d'enregistrement : | Totaal preparation tijd | ||||
10 | extra certificaten : | |||||
11 | aantal hierboven x prijs per uur | |||||
12 | totaal : | 0 | ||||
13 | ||||||
14 | ||||||
15 | ||||||
16 | ||||||
17 | ||||||
18 | ||||||
19 | Verplaatsing-déplacement (XX* aantal dagen) | |||||
20 | nazicht actieplan -verif plan action (altijd XX€) | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B5 | B5 | =D11 |
B12 | B12 | =SUM(B5:B10) |
How it's now + vba code + buttons that aren't showing beneath
Calcul Facturatie -.xlsm | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
1 | Naam Auditor | Meerdere sites? | standard | Aantal (zelfde norm) | Preparation (hours) | Reporting (hours) | certification fee | registration fee | TRUE | Preparation (hours) | Reporting (hours) | Cell right is for clear function | Naam Auditor | |||||||||||
2 | Datum Audit | <- Aantal sites | Cell right is for Bepork search | TRUE | Datum Audit | 00/01/1900 | ||||||||||||||||||
3 | Offer nummer | <- Aantal verplaatsingen | ACS | 1 | 1 | 4 | €0 | / | FALSE | FALSE | FALSE | Nederlands | Offer nummer | |||||||||||
4 | vanaf 2de | 1 | / | 2 | €0 | / | FALSE | FALSE | FALSE | Norm 1 audit uitgevoerd door bij op 30-12-1899 volgens offerte | Klanten naam | |||||||||||||
5 | Export China | / | Selecteer rapportage | €0 | / | FALSE | FALSE | FALSE | ||||||||||||||||
6 | Export Zuid-Korea | / | Selecteer rapportage | €0 | / | FALSE | FALSE | FALSE | PO / Ref. : | |||||||||||||||
7 | BVI | / | 1 | €0 | / | FALSE | FALSE | FALSE | ||||||||||||||||
8 | Febev+ rund | / | 0 | €0 | €0 | FALSE | FALSE | FALSE | Audit : | 0.00 EUR | ||||||||||||||
9 | Febev+ varken | / | 0 | €0 | / | FALSE | FALSE | FALSE | Certificatiekosten Norm 1 - Prijzen 2021 : | #N/A | ||||||||||||||
10 | Bepork | / | Selecteer rapportage | €0 | €0 | FALSE | FALSE | Verplaatsing : | 0.00 EUR | |||||||||||||||
11 | Belplume | / | 0 | €0 | €0 | FALSE | FALSE | FALSE | Nazicht actieplan : | 0.00 EUR | ||||||||||||||
12 | Totaal audit uur | BRC | 2 | 8 | €0 | €0 | FALSE | FALSE | FALSE | Registratiekosten Norm 1 : | #N/A | |||||||||||||
13 | Totaal raportage tijd | 0 | ASDA | / | 2 | €0 | €0 | FALSE | FALSE | FALSE | Extra certificaten : | 0.00 EUR | ||||||||||||
14 | Totaal preparation tijd | 0 | Meat module | / | 2 | €0 | €0 | FALSE | FALSE | FALSE | ||||||||||||||
15 | FSMA | / | 2 | €0 | €0 | FALSE | FALSE | FALSE | Totaal : | #N/A | ||||||||||||||
16 | Totaal hierboven | 0 | Gluten free | / | 2 | €0 | €0 | FALSE | FALSE | FALSE | ||||||||||||||
17 | BRC Packaging | 2 | 6 | €0 | €0 | FALSE | FALSE | FALSE | Frans | |||||||||||||||
18 | Aantal hierboven x prijs per uur | 0 | BRC S&D | 2 | 4 | €0 | €0 | FALSE | FALSE | FALSE | Norm 1 audit effectués par chez le 30-12-1899 suivant l'offre signée | |||||||||||||
19 | BRC Broker | 2 | 4 | €0 | €0 | FALSE | FALSE | FALSE | ||||||||||||||||
20 | COLRUYT | / | 2 | €0 | / | FALSE | FALSE | FALSE | PO / Ref. : | |||||||||||||||
21 | ISO 22000 | / | 4 | €0 | / | FALSE | FALSE | FALSE | ||||||||||||||||
22 | surveillance/repeat | / | 3 | / | / | FALSE | FALSE | FALSE | Audit : | 0.00 EUR | ||||||||||||||
23 | FSSC22000 | 2 | 5 | €0 | €0 | FALSE | FALSE | FALSE | Frais de certification Norm 1 - Prix 2021 : | #N/A | ||||||||||||||
24 | surveillance/repeat | 2 | 4 | / | / | FALSE | FALSE | FALSE | Déplacement : | 0.00 EUR | ||||||||||||||
25 | Head office | / | 2 | / | / | FALSE | FALSE | FALSE | Verif plan action : | 0.00 EUR | ||||||||||||||
26 | IFS | 2 | 5 | €0 | €0 | FALSE | FALSE | FALSE | Enregistrement Norm 1 : | #N/A | ||||||||||||||
27 | IFS Broker | 2 | 4 | €0 | €0 | FALSE | FALSE | FALSE | Certificats supplémentaires : | 0.00 EUR | ||||||||||||||
28 | IFS Whole Sale | 2 | 4 | €0 | €0 | FALSE | FALSE | FALSE | ||||||||||||||||
29 | IFS Logistics | 2 | 4 | €0 | €0 | FALSE | FALSE | FALSE | Total : | #N/A | ||||||||||||||
30 | IFS C&C | 2 | 4 | €0 | €0 | FALSE | FALSE | FALSE | ||||||||||||||||
31 | IFS Global Markets | 1 | 3 | €0 | €0 | FALSE | FALSE | FALSE | Engels | |||||||||||||||
32 | GRMS | / | 8 | €0 | €0 | FALSE | FALSE | FALSE | Norm 1 audit performed by at on 30-12-1899 according to quotation | |||||||||||||||
33 | MSC | / | 2 | €0 | / | FALSE | FALSE | FALSE | ||||||||||||||||
34 | * IFS + BRC combi * | 2 | 12 | / | / | FALSE | FALSE | FALSE | PO / Ref. : | |||||||||||||||
35 | ||||||||||||||||||||||||
36 | Audit : | 0.00 EUR | ||||||||||||||||||||||
37 | Export audit duur | Berekening | Rapportage tijd | Uitleg formules: | Certification fee Norm 1 - Price 2021 : | #N/A | ||||||||||||||||||
38 | Export 3de landen | 30% | 0 | Kijkt welke grootste is in kolom O | 0 | Travel cost : | 0.00 EUR | |||||||||||||||||
39 | Telt alles in kolom P | 0 | Review action plan : | 0.00 EUR | ||||||||||||||||||||
40 | Alles word gecleared tot vak B39 | Kijkt of ISO surveillance is aangeklikt | FALSE | Registration fee Norm 1 : | #N/A | |||||||||||||||||||
41 | Kosten 2021 | Kijkt of FSSC surveillance is aangeklikt | FALSE | Supplementary certificates : | 0.00 EUR | |||||||||||||||||||
42 | Actieplan | 166 | Telt 30% van het export 3de audit duur | 0 | ||||||||||||||||||||
43 | Verplaatsing binnenland per dag | 0 | Kijkt of Export 3de is aangeklikt | FALSE | Total : | #N/A | ||||||||||||||||||
44 | Uur tarief | 166 | Kijkt of export is aangelikt indien ja toont hij het aantal | FALSE | ||||||||||||||||||||
45 | Rond het export 3de landen getal af | 0 | ||||||||||||||||||||||
46 | Uknown iets met ISO | FALSE | ||||||||||||||||||||||
47 | Uknown iets met FSSC | FALSE | ||||||||||||||||||||||
48 | Telt alle rapportages samen | 0 | ||||||||||||||||||||||
49 | Kijkt of er meerdere sites zijn | FALSE | ||||||||||||||||||||||
50 | Telt het aantal sites x prep | FALSE | ||||||||||||||||||||||
51 | Telt het aantal sites x actieplan | FALSE | ||||||||||||||||||||||
52 | Telt het aantal auditdagen | 0 | ||||||||||||||||||||||
53 | ||||||||||||||||||||||||
54 | Export China lijst: | Rapportage: | True/False | Output: | ||||||||||||||||||||
55 | Selecteer rapportage | 0 | TRUE | 0 | ||||||||||||||||||||
56 | Slachthuis (A) | 4 | FALSE | FALSE | ||||||||||||||||||||
57 | Uitsnijderij (B) | 4 | FALSE | FALSE | ||||||||||||||||||||
58 | Koel/Vrieshuis (C) | 2 | FALSE | FALSE | ||||||||||||||||||||
59 | Slachthuis + Uitsnijderij (A+B) | 4 | FALSE | FALSE | ||||||||||||||||||||
60 | Slachthuis + Uitsnijderij + Koel/Vrieshuis (A+B+C) | 4 | FALSE | FALSE | ||||||||||||||||||||
61 | Uitsnijderij + Koel/Vrieshuis (B+C) | 4 | FALSE | FALSE | ||||||||||||||||||||
62 | Slachthuis + Koel/Vrieshuis (A+C) | 4 | FALSE | FALSE | ||||||||||||||||||||
63 | ||||||||||||||||||||||||
64 | Export Zuid-Korea lijst: | Rapportage: | True/False | Output: | ||||||||||||||||||||
65 | Selecteer rapportage | 0 | TRUE | 0 | ||||||||||||||||||||
66 | Slachthuis (A) | 3 | FALSE | FALSE | ||||||||||||||||||||
67 | Uitsnijderij (B) | 3 | FALSE | FALSE | ||||||||||||||||||||
68 | Koel/Vrieshuis (C) | 2 | FALSE | FALSE | ||||||||||||||||||||
69 | Slachthuis + Uitsnijderij (A+B) | 4 | FALSE | FALSE | ||||||||||||||||||||
70 | Slachthuis + Uitsnijderij + Koel/Vrieshuis (A+B+C) | 5 | FALSE | FALSE | ||||||||||||||||||||
71 | Uitsnijderij + Koel/Vrieshuis (B+C) | 4 | FALSE | FALSE | ||||||||||||||||||||
72 | Slachthuis + Koel/Vrieshuis (A+C) | 4 | FALSE | FALSE | ||||||||||||||||||||
73 | Bereiding (D) | 2 | FALSE | FALSE | ||||||||||||||||||||
74 | Uitsnijderij + Bereiding (B+D) | 4 | FALSE | FALSE | ||||||||||||||||||||
75 | ||||||||||||||||||||||||
76 | Bepork lijst: | CertificationFee | RegistrationFee | All in price | True/False | Output CertificationFee: | Output RegistrationFee: | Output All in price: | ||||||||||||||||
77 | Selecteer rapportage | €0 | €0 | €0 | TRUE | 0 | 0 | 0 | ||||||||||||||||
78 | BePork Slachthuis - Combi | €0 | €113 | €0 | FALSE | FALSE | FALSE | FALSE | ||||||||||||||||
79 | BePork Uitsnijderij - Combi | €140 | €0 | €0 | FALSE | FALSE | FALSE | FALSE | ||||||||||||||||
80 | BePork Slachthuis - Single | €0 | €0 | €300 | FALSE | FALSE | FALSE | FALSE | ||||||||||||||||
81 | BePork Uitsnijderij - Single | €0 | €0 | €260 | FALSE | FALSE | FALSE | FALSE | ||||||||||||||||
82 | ||||||||||||||||||||||||
83 | Kosten 2021 | |||||||||||||||||||||||
84 | Actieplan | 0 | ||||||||||||||||||||||
85 | Verplaatsing binnenland per dag | 0 | ||||||||||||||||||||||
86 | Uur tarief | 0 | ||||||||||||||||||||||
87 | Verplaatsing Bepork Single | 0 | ||||||||||||||||||||||
88 | Actieplan Bepork Single | 0 | ||||||||||||||||||||||
Automatic Calculation 2021 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
R4 | R4 | =VenA(R8:R13,$V$1:$V$4) |
V1 | V1 | =$C$1 & "" |
V2 | V2 | =$C$2 |
V3 | V3 | =$C$3 & "" |
V4 | V4 | =$A$2 & "" |
P3:P4,P7:P9,P11:P34 | P3 | =IF(N3,K3*I3) |
P5 | P5 | =LOOKUP(1000,SEARCH($P$55:$P$62,N5),$Q$55:$Q$62) |
P6 | P6 | =LOOKUP(1000,SEARCH($P$65:$P$74,N6),$Q$65:$Q$74) |
O3:O34 | O3 | =IF(N3,J3) |
S8,S36,S22 | S8 | =IF($R$80,$U$80,IF($R$81,$U$81,$D$18)) |
S9,S37,S23 | S9 | =LOOKUP(1000,SEARCH($H$3:$H$33,R9),$L$3:$L$33) |
S10,S38,S24 | S10 | =IF($R$80,$T$80,$O$52) |
S11,S39,S25 | S11 | =IF($R$80,$O$88,IF($R$81,$O$88,IF($O$49,$O$51,$O$84))) |
S12,S40,S26 | S12 | =LOOKUP(1000,SEARCH($H$3:$H$33,R12),$M$3:$M$33) |
D13 | D13 | =O48 |
D14 | D14 | =IF(O50,O50,O38) |
S15,S43,S29 | S15 | =SUM(S8:S13) |
D16 | D16 | =SUM(D12:D14) |
D18 | D18 | =D16*L44 |
R18 | R18 | =VenAFR(R22:R27,$V$1:$V$4) |
R32 | R32 | =VenAEN(R36:R41,$V$1:$V$4) |
K38 | K38 | =O$45 |
O38 | O38 | =MAX(O$3:O$34) |
O39 | O39 | =SUM(P$3:P$34) |
O40 | O40 | =IF(P$22<P$21,P$22) |
O41 | O41 | =IF(P$24<P$23,P$24) |
O42 | O42 | =I$38/100*30 |
L43 | L43 | =IF($R$80,$O$87,IF($R$81,$O$87,$O$85)) |
O44 | O44 | =IF(O$43,O$42) |
O45 | O45 | =IF(O$44<1,1,INT(O$44+0.5)) |
O46 | O46 | =IF(O$40,4) |
O47 | O47 | =IF(O$41,5) |
O48 | O48 | =O$39+O$45-O$46-O$47 |
O50 | O50 | =IF(O49,O38*E2) |
O51 | O51 | =IF($O$49,$L$42*$E$2) |
O52 | O52 | =$E$3*$L$43 |
P55:P62 | P55 | =IF($K$5=N55,TRUE,FALSE) |
Q55:Q62 | Q55 | =IF(P55,O55*$I$5) |
P65:P74 | P65 | =IF($K$6=N65,TRUE,FALSE) |
Q65:Q74 | Q65 | =IF(P65,O65*$I$6) |
R77:R81 | R77 | =IF($K$10=N77,TRUE,FALSE) |
S77:S81 | S77 | =IF(R77,O77) |
T77:T81 | T77 | =IF(R77,P77) |
U77:U81 | U77 | =IF(R77,Q77) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
S36 | Cell Value | beginning with "0" | text | NO |
S37,S40 | Cell | contains an error | text | NO |
S38 | Cell Value | =0 | text | NO |
S22 | Cell Value | beginning with "0" | text | NO |
S23,S26 | Cell | contains an error | text | NO |
S24 | Cell Value | =0 | text | NO |
S43 | Cell | contains an error | text | NO |
S29 | Cell | contains an error | text | NO |
S15 | Cell | contains an error | text | NO |
S8 | Cell Value | beginning with "0" | text | NO |
S9,S12 | Cell | contains an error | text | NO |
S10 | Cell Value | =0 | text | NO |
H40 | Cell Value | contains "FALSE" | text | NO |
H40 | Cell Value | contains "TRUE" | text | NO |
I41 | Other Type | Color scale | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
K5 | List | =$N$55:$N$62 |
K6 | List | =$N$65:$N$74 |
K10 | List | =$N$77:$N$81 |