Automatic List Generation

[Guest]

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?

 

[Guest]

Well, this isn't particularly elegant, but it's all I can think of right now:

For Sheet1 containing Products in Col_A (beginning in A1) and "flags" in
Col_B (flags being any character or number)

On Sheet2, this formula will list the flagged items:

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

Copy that formula down as far as needed.

Does that help?

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

XL2002, WinXP-Pro

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks, It works!

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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