Picking material from specific Lot



In the below table I am substracting quantities from C3 , from required quantities in G2.

What I want , If I am substracting 30, 3 times from C2. Is there any method of tracing that from which Lot. I have picked the quantity despite of how many times in H2.

  A B C D E F G H 1 Date Lot No Qty. Bal Qty   Batch No Used Qty Lot Used 2 10.16.16 SNQA156 100 40   A 30   3           b 30  



Please include a sample file that shows what you are trying to do.

Or reformat your table
I'm on mobile phone, and I can't understand your table.
MRVMV (rep: 52) Jul 13, '16 at 12:08 pm
I have attached the sample file.Please check .
Actually what I want to have , tracking of materials on First In First Out basis.
ansh86 Jul 13, '16 at 12:20 pm
Add to Discussion



Hello again,

Like what I understood I tried one approach (in the attached file)

I made one column that give you the sum of IN quantity before this IN and another column that gives you the sum after this new quantity,

So when you type the OUT it will search this quantity in the 2 columns to find a row that this new quantity is more than 1st column and less than 2nd column,

When it finds that row it will give The N number of this row (new column too),

Now, it will use Index match formula to index the Lot. No.

Sorry if this is complicated,

PLEASE try the attached file and mark as answer if that helped you.

(I'm not sure if using nonaccurate match with 1st sum column may shorten that but I don't use it nor recommend it)

Note: I'm not sure if date,balance quantity,batch no are important, I considered that they are not.




Issue resolved upto an extent but if I am using two out sheets than I am getting same Lot no. again and again.
ansh86 Jul 19, '16 at 3:20 am
Could you attach that file or a sample file with two sheets

The problem maybe in editing the formulas to work between sheets.
MRVMV (rep: 52) Jul 19, '16 at 6:04 am
Please find attached file named sample file excel forum
ansh86 Jul 19, '16 at 11:55 am
Any suggestion regarding my query
ansh86 Jul 21, '16 at 10:43 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login