Help with formula please

  • Thread starter Thread starter Joe Gieder
  • Start date Start date
J

Joe Gieder

Can this formula be made to list only cell results that
are numeric? Right now it also includes results that are
alphabetic.

=IF(ISERR(SMALL(IF('Priced BOM'!$X$3:$X$733>19,ROW
('Priced BOM'!$X$3:$X$733)),ROW()-ROW($I$5)+1)),"",INDEX
(INDIRECT("'Priced BOM'!$I$1:$I$733"),SMALL(IF('Priced
BOM'!$X$3:$X$733>19,ROW('Priced BOM'!$X$3:$X$733)),ROW()-
ROW($I$5)+1))) - Array Entered

What I'm trying to do (without using autofilter because
two different worksheets are involved and I'm trying to
eliminate as many additional steps as possible) is get
all the items with a delivery of over 19. This formula
works with the exception that it also includes items that
have "stk" as the cell result in place of a number. Is
there a way to modify this formula to not include
the "STK" items.

TIA for all the help
Joe
 
Without testing the formula, would this work?

=IF(ISERR(SMALL(IF(('Priced BOM'!$X$3:$X$733>19)*(ISNUMBER('Priced
BOM'!$X$3:$X$733)),ROW('Priced
BOM'!$X$3:$X$733)),ROW()-ROW($I$5)+1)),"",INDEX(INDIRECT("'Priced
BOM'!$I$1:$I$733"),SMALL(IF(('Priced BOM'!$X$3:$X$733>19)*(ISNUMBER('Priced
BOM'!$X$3:$X$733)),ROW('Priced BOM'!$X$3:$X$733)),ROW()-ROW($I$5)+1)))
 
Thanks Peo.
It works except now if the item ($I$1:$I$733) contains a
letter it skips it. Any ideas?
Joe
 
Back
Top