It does. I've created some work arounds and it now works. I appreciate your
help.
--
JerryS


"Ron Coderre" wrote:

So far, the only way I get that error is when cells in Col_B look blank, but
actually contain an apostrophe.

Because they are non-blank, they are counted by the COUNTA function.

BUT because the also equal "", they're row numbers are multiplied by
10^10...resulting in a row_ref in the INDEX file that could not possibly
exist. Hence the error.

Do you have anything like that situation existing?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JerryS" wrote:

Sorry to bother you Ron but I'm running into an error that does not make
sense. Sheet1 in column A is the product listing, column B is where items are
selected with an "x". I have this formula in A1 of Sheet2 in as many rows as
equal Sheet1:

=IF(COUNTA(Sheet1!$B$1:$B$58)<ROW(),"",INDEX(Sheet 1!$A$1:$A$58,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$5 8)+(Sheet1!$B$1:$B$58="")*10^10,ROW()))))

The formula works but is throwing out #REF errors at the bottom of the list
generated on Sheet2 as many time as items have Not been selected on Sheet1.
For example, 7 our of 10 items on Sheet1 are selected. Sheet2 lists those 7
items plus 3 #REF items.

How do I get rid of the #REF listing. Any comments are appreciated. Thanks

Jerry
--
JerryS


"Ron Coderre" wrote:

One last comment:
You don't need to use Ctrl+Shift+Enter to commit that formula...it's not an
array formula. :\

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

Sorry for the confusion.
Yes!...the Sheet3 references should be Sheet1
(I just gave myself a newspaper over the snout for forgetting to correct
that before posting)

So...on sheet2

A1:
=IF(COUNTA(Sheet1!$B$1:$B$20)<ROW(),"",INDEX(Sheet 1!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$2 0)+(Sheet1!$B$1:$B$20="")*10^10,ROW()))))

(Remember to Ctrl+Shift+Enter that array formula)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JerryS" wrote:

I'm a little thrown off. Do you mean Sheet1 where you list Sheet3? I'm not
getting any values. Thanks
--
JerryS


"JerryS" wrote:

I have a spreadsheet that lists all of our products individually in the first
column. The next column is for selecting the items wanted. I'd like on a
seperate worksheet to build a list of only those items selected. Is this
possible?
--
JerryS