Hi!
for some reason, it ignores the last short and over barcodes in 13DBC,
which is:
SHORT:
6001009002600 12.000 units (column C52)
OVER:
2906818000004 2.000 units (column C60)
You only have data down to row 52 so there's nothing to miss in "(column
C60)".
Actually, you're only missing data in the "Over" sheet.
There are 3 entries that should appear in "Short" (and they're there) and 2
entries that should appear in "Over" with only 1 being there.
You can replace your current formula with this array formula:
=IF(ROWS($1:1)<=COUNT('13DBC'!D$10:D$60),INDEX('13 DBC'!A$10:A$60,SMALL(IF('13DBC'!D$10:D$60<"",(ROW ('13DBC'!A$10:A$60)-ROW(A$10))+1),ROW(1:1))),"")
Replacing all your current formulas in column A of sheet OVER-2 with the
above formula reduced the file size by 6kb.
You can make most of your other "lookup" formulas more efficient, also.
If you recall, I made a similar suggestion to use the above formula about a
month or so ago when you posted this but you replied back about having
trouble with barcodes in TEXT and NUMBERS.
Well, with just a quick look-over I can see no reason for having the mixed
types. That's causing you to use overly complex formulas. (like the one you
posted ). When using the formula I suggested some of your lookup formulas
now return errors due to the MIXED DATA TYPES. Example: Vlookups that use a
numeric lookup value in a text lookup table and vice versa.
Formatting numeric data as text just leads to problems!
Would you like me to "clean-up" this file?
Biff
"sonar" wrote in
message ...
Hi
My code in my SHORT is:
=IF(ISERROR(INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(S MALL(ROW('13DBC'!$10:$60)*('13DBC'!$C$10:$C$60<"" ),ROW()-9+COUNTA('13DBC'!$C$10:$C$60)-COUNTIF('13DBC'!$C$10:$C$60,"0"))),COLUMN()-1)),"",INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(SMALL( ROW('13DBC'!$10:$60)*('13DBC'!$C$10:$C$60<""),ROW ()-9+COUNTA('13DBC'!$C$10:$C$60)-COUNTIF('13DBC'!$C$10:$C$60,"0"))),COLUMN()-1))
and my OVER is:
=IF(ISERROR(INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(S MALL(ROW('13DBC'!$10:$60)*('13DBC'!$D$10:$D$60<"" ),ROW()-9+COUNTA('13DBC'!$D$10:$D$60)-COUNTIF('13DBC'!$D$10:$D$60,"0"))),COLUMN()-1)),"",INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(SMALL( ROW('13DBC'!$10:$60)*('13DBC'!$D$10:$D$60<""),ROW ()-9+COUNTA('13DBC'!$D$10:$D$60)-COUNTIF('13DBC'!$D$10:$D$60,"0"))),COLUMN()-1))
for some reason, it ignores the last short and over barcodes in 13DBC,
which is:
SHORT:
6001009002600 12.000 units (column C52)
OVER:
2906818000004 2.000 units (column C60)
It does not give the same problem in my 8DBC's please help
My file is a little big, and I cant make it any smaller.
here it is http://www.epping.co.za/stockproblem.zip
you can take the password off as follows: ToolsProtectionunprotect.
there is no password.
Thanks.
Sonar 
--
sonar
------------------------------------------------------------------------
sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=470611