Array formula

[gordo]

Hi,
I wonder if someone can help me i have the following formula:

=IF($P20=1,IF($D20="",MAX('C:\unzipped\cycle\[cycle counts
sep2005.xls]Recounted SKU''s'!$F$2:$F$400*IF('C:\unzipped\cycle\[cycle
counts sep2005.xls]Recounted SKU''s'!$A$2:$A$400=B20,1,0))))

the purpose of the formula above is to bring back the last qty of a sku
that has been counted, however this only works if the last qty is more
than the previous, so for example:

1)SKU 125672 qty 120 units date 4/10/2005
2)SKU 126666 qty 110 date 5/10/2005
3)SKU 125672 qty 110 units date 6/10/2005

so in the example above excel would bring back number 1 as number 3 has
less units
A sku can be counted numerous times with the date changing dependant
when it was counted. I therefore have a formula which brings back the
last time the SKU was counted but i also want to bring back the qty.

many thanks

Gordon

 

[Harlan Grove]

gordo wrote...
....

=IF($P20=1,IF($D20="",MAX('C:\unzipped\cycle\[cycle counts
sep2005.xls]Recounted SKU''s'!$F$2:$F$400*IF('C:\unzipped\cycle\[cycle
counts sep2005.xls]Recounted SKU''s'!$A$2:$A$400=B20,1,0))))

the purpose of the formula above is to bring back the last qty of a sku
that has been counted, however this only works if the last qty is more
than the previous, so for example:

....

If you want the last quantity for a given SKU, you need a lookup of
some sort rather than a MAX call. If your table were sorted in
ascending order by date, SKUs were in column A and units sold in column
F, then it could look something like

=IF($P20=1,IF($D20="",LOOKUP(2,1/('<whatever>'!$A$2:$A$400=B20),
'<whatever>'!$F$2:$F$400),""),"")