|
Automatic List Generation
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 |
Automatic List Generation
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(Sheet 3!$A$1:$A$20,SUMPRODUCT(SMALL(ROW(Sheet3!$B$1:$B$2 0)+(Sheet3!$B$1:$B$20="")*10^10,ROW())))) Copy that formula down as far as needed. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "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 |
Automatic List Generation
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 |
Automatic List Generation
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 |
Automatic List Generation
Thanks, It works!
-- 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 |
Automatic List Generation
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 |
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(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 |
Automatic List Generation
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 |
Automatic List Generation
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 |
| All times are GMT +1. The time now is 07:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com