Prev: I want to copy/export a range of cells into another program
Next: multiple calculations in 1 cell
From: Karl on 5 Mar 2010 15:26
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 are:
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

From: JLatham on 7 Mar 2010 07:52
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 are:
> 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
>
 | 
Pages: 1
Prev: I want to copy/export a range of cells into another program
Next: multiple calculations in 1 cell