Automatic List Generation

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(Sheet1!$A$1:$A$58,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$58)+(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(Sheet1!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$20)+(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

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(Sheet1!$A$1:$A$58,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$58)+(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(Sheet1!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$20)+(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

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(Sheet1!$A$1:$A$58,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$58)+(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(Sheet1!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$20)+(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