himurah182
New Member
- Joined
- Aug 4, 2022
- Messages
- 21
- Office Version
- 2019
- Platform
- Windows
Hey guys, my problem is the following:
I have this sheet where I need to calculate the value of each type of products that were sold to every customer I have.
In the first sheet I insert the data and then in the second sheet only the final values appear.
For some reason, invoice 1276 with a value of 471,78 appears as 459,99 in the second sheet.
Why is this happening?
I have this sheet where I need to calculate the value of each type of products that were sold to every customer I have.
In the first sheet I insert the data and then in the second sheet only the final values appear.
For some reason, invoice 1276 with a value of 471,78 appears as 459,99 in the second sheet.
Why is this happening?
Calculador BP Julho.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Faturas | |||||||
2 | ||||||||
3 | ||||||||
4 | 0,00 | |||||||
5 | Fatura | 1276 | ||||||
6 | Where code | 27033 | ||||||
7 | Plu | Produto | Preço Und | BP Grândola - Este | ||||
8 | type 1 | 103581 | RPBLACK2-BLACK ULTRA THIN 1,1/4 | 0,443 | 100 | |||
9 | type 1 | 103582 | RPBLACK3-BLACK KING SIZE | 0,483 | ||||
10 | type 1 | 103583 | BOLSA FILTROS REGULAR-8MM-30B-100F | 0,470 | 30 | |||
11 | type 1 | 103584 | BOLSA FILTROS SLIM-6MM-34B-120F | 0,422 | 34 | |||
12 | type 1 | 103585 | ISQUEIRO CLIPPER PEQ. CP22RH | 0,592 | 144 | |||
13 | type 1 | 103586 | ISQUEIRO CLIPPER PEQ FUN | 1,726 | ||||
14 | type 1 | 103587 | ISQUERIRO CLIPPER GRD CP11RH | 0,678 | 144 | |||
15 | type 1 | 103902 | ISQUEIRO CLIPPER CLASS.METAL LARG CAIXA | 6,581 | ||||
16 | type 1 | 103903 | BOLSA FILTROS LONG SLIM-6MM-30B-100F | 0,543 | 30 | |||
17 | type 1 | 103904 | BOLSA FILTROS LONG REGULAR-8MM-20B-100F | 0,543 | ||||
18 | type 1 | 105457 | ISQUEIRO Clipper Classic POP CPR11H | 1,711 | ||||
19 | type 1 | 106629 | ISQUEIRO MINI CLIPPER TUBE | 0,897 | ||||
20 | type 1 | 106630 | PAPEL CLIPPER BLUE ULTRA THIN REGULAR | 0,351 | ||||
21 | type 1 | 106631 | PAPEL CLIPPER PURE ULTRA THIN REGULAR | 0,351 | ||||
22 | type 1 | 106632 | PAPEL CLIPPERPURE ULTRA THIN KSS | 0,531 | ||||
23 | type 1 | 106633 | PAPEL CLIPPER RED REGULAR CLASSIC | 0,330 | 100 | |||
24 | type 1 | 106634 | PAPEL CLIPPER GREEN CUT CORNERS REGULAR | 0,330 | ||||
25 | type 1 | 106636 | MÁQUINA ENROLAR | 1,655 | ||||
26 | type 1 | 106637 | FILTROS POP UP EXTRA SLIM | 0,668 | ||||
27 | type 1 | 106638 | MEN-FILTROS POP UP EXTRA SLIM MENTOL | 0,744 | ||||
28 | type 1 | 106639 | FILTROS SLIM ORGANIC BOLSA | 0,632 | ||||
29 | type 1 | 107670 | SET KING SIZE SLIM+FILTROS DE PAPEL | 0,839 | 120 | |||
30 | type 1 | 116773 | PAPEL RAW ORG HEMP KING SIZE SLIM | 0,561 | 50 | |||
31 | type 1 | 116774 | PAPEL RAW ORG REGULAR | 0,375 | ||||
32 | type 1 | 116775 | PAPEL RAW CLASSIC KING SIZE SLIM | 0,531 | 50 | |||
33 | type 1 | 116776 | FILTROS RAW ORGANIC SLIM | 0,634 | ||||
34 | Type 2 | 103588 | RELÓGIO Medium Price | 16,770 | ||||
35 | Type 2 | 103589 | RELÓGIO Low Price | 13,406 | ||||
36 | Type 2 | 103905 | RELOGIO ANALOGICO | 26,861 | ||||
37 | Type 2 | 106640 | SMARTWATCH SAMI | 44,743 | ||||
38 | Type 3 | 106506 | CIGARREIRA CAIXA PLASTICO | 2,584 | ||||
39 | Type 3 | 106506 | CIGARREIRA SILICONE | 2,584 | ||||
40 | Type 3 | 106661 | Cigarreira Cartão | 2,584 | ||||
41 | Type 3 | 106506 | CIGARREIRA CAIXA METAL | 2,584 | ||||
42 | Type 3 | 103906 | LINHA PORTUGAL PORTA MOEDAS | 3,305 | ||||
43 | Type 3 | 103907 | LINHA PORTUGAL ESTOJO | 4,378 | ||||
44 | Type 3 | 103908 | LINHA PORTUGAL CARTEIRA 2 | 5,548 | ||||
45 | Type 3 | 103909 | LINHA PORTUGAL CARTEIRA | 5,548 | ||||
46 | Type 3 | 103910 | CARTEIRA PRIMAVERA C/FECHO 119 BAND | 5,509 | ||||
47 | Type 3 | 103911 | CARTEIRA PRIMAVERA GRANDE 110 | 4,378 | ||||
48 | Type 3 | 103912 | CARTEIRA PRIMAVERA PEQUEÑA 111 | 3,949 | ||||
49 | Type 3 | 103913 | MATRAFONA GIGANTE | 11,164 | ||||
50 | Type 3 | 103914 | BLOCO NOTAS | 1,570 | ||||
51 | Type 3 | 103915 | PULSERIRA PELE COM CAIXA | 12,275 | ||||
52 | Type 3 | 103916 | LICOREIRA CORTIÇA | 6,679 | ||||
53 | Type 3 | 103917 | PULSEIRAS CORTIÇA | 6,679 | ||||
54 | Type 3 | 103918 | COLARES CORTIÇA | 7,790 | ||||
55 | Type 3 | 103919 | CANETA CORTIÇA COM CAIXA | 5,548 | ||||
56 | Type 3 | 106507 | PELUCHE TAM 1 | 10,033 | ||||
57 | Type 3 | 106508 | PELUCHE TAM 3 | 15,649 | ||||
58 | Type 3 | 106509 | OCULOS SOL | 15,298 | ||||
59 | Type 3 | 106641 | CARTAS JOGAR PLÁSTICO | 5,070 | ||||
60 | Type 3 | 106642 | CARTEIRA GB SRA PELE | 24,619 | ||||
61 | Type 3 | 106643 | CARTEIRA GB HM PELE-PEQ | 14,518 | ||||
62 | Type 3 | 106644 | CARTEIRA GB HM PELE- GR | 16,770 | ||||
63 | Type 3 | 106645 | SACA ROLHAS CORTIÇA | 2,779 | ||||
64 | Type 3 | 106654 | CIGARREIRAS CORTIÇA | 5,606 | ||||
65 | Type 3 | 106655 | MINIATURA V. PORTO/LICOR BEIRÃO | 3,364 | ||||
66 | Type 3 | 106656 | CONJ 2 MINIATURAS PORTO+BEIRÃO | 5,070 | ||||
67 | Type 3 | 106657 | PORTA MOEDAS PEQ CORTIÇA-VÁRIOS MODELOS | 4,436 | ||||
68 | Type 3 | 106658 | PORTA MOEDAS DA AVÓ-CORTIÇA | 7,703 | ||||
69 | Type 3 | 106659 | PORTA MOEDAS HM -CORTIÇA | 8,921 | ||||
70 | Type 3 | 106660 | PORTA MOEDAS MOCHILA CORTIÇA | 5,899 | ||||
71 | Type 3 | 107671 | PUZZLES- SORTIDOS | 3,364 | ||||
72 | Type 3 | 107672 | JOGO TABULEIRO-SORTIDO | 6,143 | ||||
73 | Type 3 | 107673 | OVO DINOSSAURO | 3,081 | ||||
74 | Type 3 | 107674 | CONJ CORTIÇA | 11,164 | ||||
75 | type 4 | 103804 | BORDA D'ÁGUA | 1,560 | ||||
76 | type 5 | 114755 | TUBOS T&T 500 UND | 2,000 | ||||
77 | type 5 | 115903 | TUBOS KORONA 300 UND | 1,111 | ||||
78 | type 5 | 115902 | TUBOS KORONA 500 UND | 2,000 | ||||
79 | 0,00 | 471,78 | ||||||
Fatura |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E4 | E4 | =E79 |
E6 | E6 | =IFNA(INDEX(Postos!B3:B220,MATCH(Fatura!E7,Postos!C3:C220,0)),"") |
D8:D75 | D8 | =D84*(1-$A$82) |
D76:D78 | D76 | =D152*(1-$B$82) |
E79:F79 | E79 | =SUMPRODUCT(E8:E78,$D$84:$D$154) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
E7:F7 | List | =Postos!$D$3# |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C219:G219 | C219 | =SUMPRODUCT(ROUND((Fatura!$D$8:$D$78)*(Fatura!$E$8:$GA$78),2)*(Fatura!$A$8:$A$78=C$1)*(Fatura!$E$6:$GA$6=$A219)) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
C3:G220 | Cell Value | =0 | text | NO |
C3:G220 | Cell Value | <0 | text | NO |