Hello all
Hoping someone can help me here.
We have a sales sheet that uses VLOOKUP to find the batch number to each product. So when someone adds a sale for beer it finds the correct batch number which helps to correctly invoice. However sometimes we'll only have 1 case left, and someone adds 2 cases to the sheet and its invoiced as such and it creates a deficit and gain across batches. We've put a condition in where if quantity is < 0 it'll return an error, and once error is reached you have to manually change the batch numbers
We're trying to find a formula where when the quantity of batch 1000 reaches zero it will return batch 1001 and so on.
Currently we have
(G1 is just for testing at the moment) where if we make the quantity of C2 = 0 then the batch moves over to 1002.
Is there an easier way for this to all come together?
Hoping someone can help me here.
We have a sales sheet that uses VLOOKUP to find the batch number to each product. So when someone adds a sale for beer it finds the correct batch number which helps to correctly invoice. However sometimes we'll only have 1 case left, and someone adds 2 cases to the sheet and its invoiced as such and it creates a deficit and gain across batches. We've put a condition in where if quantity is < 0 it'll return an error, and once error is reached you have to manually change the batch numbers
We're trying to find a formula where when the quantity of batch 1000 reaches zero it will return batch 1001 and so on.
Currently we have
Excel Formula:
=IF(VLOOKUP(G1,A1:C9,3,)=0,VLOOKUP(G1,A1:C9,2),VLOOKUP(G1,A1:C9,2,))
Is there an easier way for this to all come together?