software

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

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 quot;flagsquot; 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)lt;ROW(),quot;quot;,INDEX(Sheet 3!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet3!$B$1:$B$2 0) (Sheet3!$B$1:$B$20=quot;quot;)*10^10,ROW()))))

Copy that formula down as far as needed.

Does that help?

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

XL2002, WinXP-Proquot;JerrySquot; wrote:

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

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

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

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)lt;ROW(),quot;quot;,INDEX(Sheet 1!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$2 0) (Sheet1!$B$1:$B$20=quot;quot;)*10^10,ROW()))))

(Remember to Ctrl Shift Enter that array formula)Does that help?

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

XL2002, WinXP-Proquot;JerrySquot; wrote:

gt; I'm a little thrown off. Do you mean Sheet1 where you list Sheet3? I'm not
gt; getting any values. Thanks
gt; --
gt; JerryS
gt;
gt;
gt; quot;JerrySquot; wrote:
gt;
gt; gt; I have a spreadsheet that lists all of our products individually in the first
gt; gt; column. The next column is for selecting the items wanted. I'd like on a
gt; gt; seperate worksheet to build a list of only those items selected. Is this
gt; gt; possible?
gt; gt; --
gt; gt; JerryS

Thanks, It works!
--
JerrySquot;JerrySquot; wrote:

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

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-Proquot;Ron Coderrequot; wrote:

gt; Sorry for the confusion.
gt; Yes!...the Sheet3 references should be Sheet1
gt; (I just gave myself a newspaper over the snout for forgetting to correct
gt; that before posting)
gt;
gt; So...on sheet2
gt;
gt; A1:
gt; =IF(COUNTA(Sheet1!$B$1:$B$20)lt;ROW(),quot;quot;,INDEX(Sheet 1!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$2 0) (Sheet1!$B$1:$B$20=quot;quot;)*10^10,ROW()))))
gt;
gt; (Remember to Ctrl Shift Enter that array formula)
gt;
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;JerrySquot; wrote:
gt;
gt; gt; I'm a little thrown off. Do you mean Sheet1 where you list Sheet3? I'm not
gt; gt; getting any values. Thanks
gt; gt; --
gt; gt; JerryS
gt; gt;
gt; gt;
gt; gt; quot;JerrySquot; wrote:
gt; gt;
gt; gt; gt; I have a spreadsheet that lists all of our products individually in the first
gt; gt; gt; column. The next column is for selecting the items wanted. I'd like on a
gt; gt; gt; seperate worksheet to build a list of only those items selected. Is this
gt; gt; gt; possible?
gt; gt; gt; --
gt; gt; gt; JerryS

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 quot;xquot;. I have this formula in A1 of Sheet2 in as many rows as
equal Sheet1:

=IF(COUNTA(Sheet1!$B$1:$B$58)lt;ROW(),quot;quot;,INDEX(Sheet 1!$A$1:$A$58,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$5 8) (Sheet1!$B$1:$B$58=quot;quot;)*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
--
JerrySquot;Ron Coderrequot; wrote:

gt; One last comment:
gt; You don't need to use Ctrl Shift Enter to commit that formula...it's not an
gt; array formula. :\
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Sorry for the confusion.
gt; gt; Yes!...the Sheet3 references should be Sheet1
gt; gt; (I just gave myself a newspaper over the snout for forgetting to correct
gt; gt; that before posting)
gt; gt;
gt; gt; So...on sheet2
gt; gt;
gt; gt; A1:
gt; gt; =IF(COUNTA(Sheet1!$B$1:$B$20)lt;ROW(),quot;quot;,INDEX(Sheet 1!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$2 0) (Sheet1!$B$1:$B$20=quot;quot;)*10^10,ROW()))))
gt; gt;
gt; gt; (Remember to Ctrl Shift Enter that array formula)
gt; gt;
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;JerrySquot; wrote:
gt; gt;
gt; gt; gt; I'm a little thrown off. Do you mean Sheet1 where you list Sheet3? I'm not
gt; gt; gt; getting any values. Thanks
gt; gt; gt; --
gt; gt; gt; JerryS
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;JerrySquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have a spreadsheet that lists all of our products individually in the first
gt; gt; gt; gt; column. The next column is for selecting the items wanted. I'd like on a
gt; gt; gt; gt; seperate worksheet to build a list of only those items selected. Is this
gt; gt; gt; gt; possible?
gt; gt; gt; gt; --
gt; gt; gt; gt; 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 quot;quot;, 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-Proquot;JerrySquot; wrote:

gt; Sorry to bother you Ron but I'm running into an error that does not make
gt; sense. Sheet1 in column A is the product listing, column B is where items are
gt; selected with an quot;xquot;. I have this formula in A1 of Sheet2 in as many rows as
gt; equal Sheet1:
gt;
gt; =IF(COUNTA(Sheet1!$B$1:$B$58)lt;ROW(),quot;quot;,INDEX(Sheet 1!$A$1:$A$58,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$5 8) (Sheet1!$B$1:$B$58=quot;quot;)*10^10,ROW()))))
gt;
gt; The formula works but is throwing out #REF errors at the bottom of the list
gt; generated on Sheet2 as many time as items have Not been selected on Sheet1.
gt; For example, 7 our of 10 items on Sheet1 are selected. Sheet2 lists those 7
gt; items plus 3 #REF items.
gt;
gt; How do I get rid of the #REF listing. Any comments are appreciated. Thanks
gt;
gt; Jerry
gt; --
gt; JerryS
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; One last comment:
gt; gt; You don't need to use Ctrl Shift Enter to commit that formula...it's not an
gt; gt; array formula. :\
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Sorry for the confusion.
gt; gt; gt; Yes!...the Sheet3 references should be Sheet1
gt; gt; gt; (I just gave myself a newspaper over the snout for forgetting to correct
gt; gt; gt; that before posting)
gt; gt; gt;
gt; gt; gt; So...on sheet2
gt; gt; gt;
gt; gt; gt; A1:
gt; gt; gt; =IF(COUNTA(Sheet1!$B$1:$B$20)lt;ROW(),quot;quot;,INDEX(Sheet 1!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$2 0) (Sheet1!$B$1:$B$20=quot;quot;)*10^10,ROW()))))
gt; gt; gt;
gt; gt; gt; (Remember to Ctrl Shift Enter that array formula)
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;JerrySquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I'm a little thrown off. Do you mean Sheet1 where you list Sheet3? I'm not
gt; gt; gt; gt; getting any values. Thanks
gt; gt; gt; gt; --
gt; gt; gt; gt; JerryS
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;JerrySquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have a spreadsheet that lists all of our products individually in the first
gt; gt; gt; gt; gt; column. The next column is for selecting the items wanted. I'd like on a
gt; gt; gt; gt; gt; seperate worksheet to build a list of only those items selected. Is this
gt; gt; gt; gt; gt; possible?
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; JerryS

It does. I've created some work arounds and it now works. I appreciate your
help.
--
JerrySquot;Ron Coderrequot; wrote:

gt; So far, the only way I get that error is when cells in Col_B look blank, but
gt; actually contain an apostrophe.
gt;
gt; Because they are non-blank, they are counted by the COUNTA function.
gt;
gt; BUT because the also equal quot;quot;, they're row numbers are multiplied by
gt; 10^10...resulting in a row_ref in the INDEX file that could not possibly
gt; exist. Hence the error.
gt;
gt; Do you have anything like that situation existing?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;JerrySquot; wrote:
gt;
gt; gt; Sorry to bother you Ron but I'm running into an error that does not make
gt; gt; sense. Sheet1 in column A is the product listing, column B is where items are
gt; gt; selected with an quot;xquot;. I have this formula in A1 of Sheet2 in as many rows as
gt; gt; equal Sheet1:
gt; gt;
gt; gt; =IF(COUNTA(Sheet1!$B$1:$B$58)lt;ROW(),quot;quot;,INDEX(Sheet 1!$A$1:$A$58,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$5 8) (Sheet1!$B$1:$B$58=quot;quot;)*10^10,ROW()))))
gt; gt;
gt; gt; The formula works but is throwing out #REF errors at the bottom of the list
gt; gt; generated on Sheet2 as many time as items have Not been selected on Sheet1.
gt; gt; For example, 7 our of 10 items on Sheet1 are selected. Sheet2 lists those 7
gt; gt; items plus 3 #REF items.
gt; gt;
gt; gt; How do I get rid of the #REF listing. Any comments are appreciated. Thanks
gt; gt;
gt; gt; Jerry
gt; gt; --
gt; gt; JerryS
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; One last comment:
gt; gt; gt; You don't need to use Ctrl Shift Enter to commit that formula...it's not an
gt; gt; gt; array formula. :\
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Sorry for the confusion.
gt; gt; gt; gt; Yes!...the Sheet3 references should be Sheet1
gt; gt; gt; gt; (I just gave myself a newspaper over the snout for forgetting to correct
gt; gt; gt; gt; that before posting)
gt; gt; gt; gt;
gt; gt; gt; gt; So...on sheet2
gt; gt; gt; gt;
gt; gt; gt; gt; A1:
gt; gt; gt; gt; =IF(COUNTA(Sheet1!$B$1:$B$20)lt;ROW(),quot;quot;,INDEX(Sheet 1!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet1!$B$1:$B$2 0) (Sheet1!$B$1:$B$20=quot;quot;)*10^10,ROW()))))
gt; gt; gt; gt;
gt; gt; gt; gt; (Remember to Ctrl Shift Enter that array formula)
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;JerrySquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I'm a little thrown off. Do you mean Sheet1 where you list Sheet3? I'm not
gt; gt; gt; gt; gt; getting any values. Thanks
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; JerryS
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;JerrySquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I have a spreadsheet that lists all of our products individually in the first
gt; gt; gt; gt; gt; gt; column. The next column is for selecting the items wanted. I'd like on a
gt; gt; gt; gt; gt; gt; seperate worksheet to build a list of only those items selected. Is this
gt; gt; gt; gt; gt; gt; possible?
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; JerryS