anya'z blog

Formula Untuk Sheet 1

kolom A2=PROPER("kode pelanggan")
kolom B2=PROPER("nama pelanggan")
kolom C2=PROPER("alamat pelanggan")
kolom D2=PROPER("no tlp")

Formula Untuk Sheet 3
kolomD4 =IF(D3="AAA",VLOOKUP(Sheet1!A3,Sheet1!$A$2:$D$7,2,0),
IF(D3="ABB",VLOOKUP(Sheet1!A4,Sheet1!$A$2:$D$7,2,0),IF(D3="ABC",VLOOKUP
(Sheet1!A5,Sheet1!$A$2:$D$7,2,0),IF(D3="ABD",VLOOKUP(Sheet1!A6,Sheet1!$A$2:$D$7,2,0),
IF(D3="ABE",VLOOKUP(Sheet1!A7,Sheet1!$A$2:$D$7,2,0))))))

kolomD5 =IF(D3="AAA",VLOOKUP(Sheet1!A3,Sheet1!$A$2:$D$7,3,0),IF(D3="ABB",VLOOKUP
(Sheet1!A4,Sheet1!$A$2:$D$7,3,0),IF(D3="ABC",VLOOKUP(Sheet1!A5,Sheet1!$A$2:$D$7,3,0),
IF(D3="ABD",VLOOKUP(Sheet1!A6,Sheet1!$A$2:$D$7,3,0),
IF(D3="ABE",VLOOKUP(Sheet1!A7,Sheet1!$A$2:$D$7,3,0))))))

KolomD6 =IF(D3="AAA",VLOOKUP(Sheet1!A3,Sheet1!$A$2:$D$7,4,0),IF(D3="ABB",VLOOKUP
(Sheet1!A4,Sheet1!$A$2:$D$7,4,0),IF(D3="ABC",VLOOKUP(Sheet1!A5,Sheet1!$A$2:$D$7,4,0),
IF(D3="ABD",VLOOKUP(Sheet1!A6,Sheet1!$A$2:$D$7,4,0),IF(D3="ABE",VLOOKUP
(Sheet1!A7,Sheet1!$A$2:$D$7,4,0))))))

kolomD9 =VLOOKUP(C9,Sheet2!$A$3:$D$7,2)

KolomE9 =VLOOKUP(C9,Sheet2!$A$3:$D$7,3)

KolomG9 =VLOOKUP(C9,Sheet2!$A$3:$D$7,4)

KolomH9 =F9*G9

KolomH15 =SUM(H9:H13)

KolomH19 =(H17-H15)

KolomC24 =terbilang(H19)