My formula stop working in the middle row position

Copper Contributor

Hello there, 

In simple description, I just make an cigarettes inventory checklist for my company.

 

I have 1 excel file containing 2 sheets inside.
The first sheet is called "Data Barang", and the second sheet is "Log Pemakaian".

The first sheet "Data Barang" in C column range contains the Item Code value, next to it range from D column contain Item Name, then E column contain initial stock of items, lastly F contain final stock of items

The second sheet "Log Pemakaian" in C column contains item code value formula automaticaly taken Data Validation from first sheet.

 

Formula should be working like this:
First sheet "Data Barang" on F column is where my formula located, if the column F from "Log Pemakaian" contain value number look for the item code from cell C3 then reduce it from initial stock of items at first sheet "Data Barang" which contain initial stock of items in E columns within the same name of item code respectively.

 

 

=IF(ISNUMBER(@'Log Pemakaian'!$F:$F), E3 - SUMIF('Log Pemakaian'!$C:$C, C3, 'Log Pemakaian'!$F:$F), E3)

 

 

 

The issue is:
My formula stopped working when it reach Row 28 "Kansas" item name, no mater what I type for usage and quantity from second sheet its just keep displaying initial stock at final stock column.
See the excel file from here.

1. Insert cigarettes name in "Log Pemakaian" sheet, example I choose "Kansas"(where formula happen error) cigarettes and the item code beside should be automatically pop.
2. Insert any amount of quantity.

3. Look at Data Barang sheet, its not change.

4. Starting from here till bottom formula not displaying final stock. 

 

I really need your help to inspect and see what is wrong with my sheet formula, i'm so frustated even ChatGPT can't help me.
Also my friend around here doesn't have knowledge for excell formulas.
I'll be very thankful if anyone can help me :)

5 Replies

@RezaRenaldi 

Change the formula in the Stock Akhir column to

=[@[Stock Awal]] - SUMIF('Log Pemakaian'!$C:$C, [@Kode], 'Log Pemakaian'!$F:$F)

or even better,

=[@[Stock Awal]] - SUMIF(Table3[Kode], [@Kode], Table3[Jumlah])

@RezaRenaldi 

I don't know what the ISNUMBER() part is for.

 

Using structured references:

=[@[Stock Awal]] - SUMIF(Table3[Kode], [@Kode];Table3[Jumlah])

I deleted all blank rows.

 

There is a formula in sheet "Log Pemakaian" which refers back to sheet "Data Barang". And the formula in sheet "Data Barang" refers back to sheet "Log Pemakaian". That is not a good practise.

 

Changing the formula in sheet "Log Pemakaian" to structured references:

=INDEX(Table4[Kode],MATCH([@[Nama Rokok]],Table4[Nama Rokok],0))

 

@RezaRenaldi 

Please try

=[@[Stock Awal]]-  SUMIF(Table3[Kode], [@Kode],Table3[Jumlah])

(see in column Test attached)

@Sergei Baklan @Hans Vogelaar @Detlef Lewin Thank you so much for your help.

My formula working fine now on both sheet, wish you always have good life for your kindness :) 

@RezaRenaldi , you are welcome, glad to help