Index or Lookup? Subtraction and conditional formatting.
Hi!
I would like to automatically calculate the remaining inventory based on the selected product and the quantity entered.
The inventory data is located in a separate table.
For example:
C3: Product
D3: Quantity
E3: Where the formula should be placed
I2:I24 : Range where inventory quantities are located
J2:J24 : Range where product names are located.
C3 is a dropdown-list based on range J2:J24.
The formula should do the following:
– Find the match of C3 in J2:J24
– Check the inventory quantity from the match of C3 in I2:24
– Subtract the amount in D3 with the inventory quantity
Adding to that I would like to add conditional formatting that if the value of E3 is smaller or equal to 0 than the inventory quantity (I2:24) of that same product (C3 and J2:J24) the color of the cell changes to red.
Is this possible? I tried with ChatGPT but unfortunately, the formulas I received are not correct or not applicable in my case, and I’m getting an error message (without explanation).
These are the codes I received from ChatGPT:
=INDEX(I:I, MATCH(C3, J:J, 0)) – D3
=IFERROR(INDEX(‘Sheet2’!$A$2:$A$100, MATCH(C3, ‘Sheet2’!$B$2:$B$100, 0)) – D3, “”)
=IFERROR(INDEX(‘Inhoud’!$A$2:$A$100, MATCH(C2, ‘Inhoud’!$B$2:$B$100, 0)) – D2, “”)
=IFERROR(VLOOKUP(C2, ‘Inhoud’!$B$2:$A$100, 2, FALSE) – D2, “”)
Thanks in advance for any help!
Hi! I would like to automatically calculate the remaining inventory based on the selected product and the quantity entered.The inventory data is located in a separate table.For example:C3: ProductD3: QuantityE3: Where the formula should be placedI2:I24 : Range where inventory quantities are locatedJ2:J24 : Range where product names are located. C3 is a dropdown-list based on range J2:J24. The formula should do the following:- Find the match of C3 in J2:J24- Check the inventory quantity from the match of C3 in I2:24- Subtract the amount in D3 with the inventory quantity Adding to that I would like to add conditional formatting that if the value of E3 is smaller or equal to 0 than the inventory quantity (I2:24) of that same product (C3 and J2:J24) the color of the cell changes to red. Is this possible? I tried with ChatGPT but unfortunately, the formulas I received are not correct or not applicable in my case, and I’m getting an error message (without explanation).These are the codes I received from ChatGPT:=INDEX(I:I, MATCH(C3, J:J, 0)) – D3=IFERROR(INDEX(‘Sheet2’!$A$2:$A$100, MATCH(C3, ‘Sheet2’!$B$2:$B$100, 0)) – D3, “”)=IFERROR(INDEX(‘Inhoud’!$A$2:$A$100, MATCH(C2, ‘Inhoud’!$B$2:$B$100, 0)) – D2, “”)=IFERROR(VLOOKUP(C2, ‘Inhoud’!$B$2:$A$100, 2, FALSE) – D2, “”)Thanks in advance for any help! Read More