If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Basing math on duplicate entries in a formula
Okay, what I want is semi complicated, but should be simple.
I have two excel sheets, one has raw data on it, let's say its an inventory sheet with Widget A and Cog B, in column B, C & D has the QTY on hand let's say 50,000 each for the months of March, April and May respectively. In the 2nd sheet, my columns a Customer, Order Date, Item ID, QTY Available, QTY Ordered, QTY remaining (for the month). Now, for the question: I've got half the formula written, it uses a VLOOKUP function to reference how many widgets or cogs are available for the month of the Order Date using the Item ID, here is the formula thus far: =IF(ISBLANK($E8),,VLOOKUP(E8,'Forecast Data'!$A$2:$O$45,$F8,FALSE)) which populates the cell with the QTY available for the matching data in E8. $F8 is a hidden cell with a column number based on the difference between the month of the order date and today. I want to add an IF statement to the VLOOKUP where VLOOKUP becomes the false execution statement and is a result of this test: I want to check backwards from the current row upwards in the sheet, stopping at row 2 to see whether or not the Date and the Item ID of the current row matches any rows above. If the statement is true, then someone has ordered this item before and I want to use the data in the QTY Remaining column of THAT row instead of using the VLOOKUP function... I hope that's a clear question, hehe |
#2
|
|||
|
|||
Basing math on duplicate entries in a formula
If I understand correctly, this formula (for cell in row 8) should do it:
=IF(SUMPRODUCT(--(B$2:B7=B8),--(C$2:C7=C8))0,"True action","False: Your VLookupHere") I believe column B has the date of order, with column C holding the product ID. The SUMPRODUCT() portion returns zero if there are no matching pair of entries on the sheet above the row with the formula, otherwise it returns the number of matching pairs of date and product ID. "Karl" wrote: Okay, what I want is semi complicated, but should be simple. I have two excel sheets, one has raw data on it, let's say its an inventory sheet with Widget A and Cog B, in column B, C & D has the QTY on hand let's say 50,000 each for the months of March, April and May respectively. In the 2nd sheet, my columns a Customer, Order Date, Item ID, QTY Available, QTY Ordered, QTY remaining (for the month). Now, for the question: I've got half the formula written, it uses a VLOOKUP function to reference how many widgets or cogs are available for the month of the Order Date using the Item ID, here is the formula thus far: =IF(ISBLANK($E8),,VLOOKUP(E8,'Forecast Data'!$A$2:$O$45,$F8,FALSE)) which populates the cell with the QTY available for the matching data in E8. $F8 is a hidden cell with a column number based on the difference between the month of the order date and today. I want to add an IF statement to the VLOOKUP where VLOOKUP becomes the false execution statement and is a result of this test: I want to check backwards from the current row upwards in the sheet, stopping at row 2 to see whether or not the Date and the Item ID of the current row matches any rows above. If the statement is true, then someone has ordered this item before and I want to use the data in the QTY Remaining column of THAT row instead of using the VLOOKUP function... I hope that's a clear question, hehe |
Thread Tools | |
Display Modes | |
|
|