| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignoring Blank value in a drop down list
Hi,
I have a dynamic drop down validation List, which comes from another spreadsheet list and have a few random blank cells in it. I need to drop these blank values from the List without making any change in the parent list. I may also have some duplicate values in the List and need to drop them too. Please help. Thanks, Shail |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring Blank value in a drop down list
"shail" skrev i en meddelelse
oups.com... Hi, I have a dynamic drop down validation List, which comes from another spreadsheet list and have a few random blank cells in it. I need to drop these blank values from the List without making any change in the parent list. I may also have some duplicate values in the List and need to drop them too. Please help. Thanks, Shail Hi Shail Here's one way to do it: Assumptions: Name of the range of your current validation list: OldList 1. Make a new list (named NewList), with a number of cells which will never be reached in ordinary use. I have made the list in C3:C24. 2. C2 must be empty or must not contain data found in OldList. The list cannot start in row 1 with the formula used. 3. In C3 enter this array formula: =INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1) The formula must be entered with <Shift<Ctrl<Enter, also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed in braces {}. Don't enter these yourself. It's Excel's way of telling, that the formula is an array formula. 4. Copy C3 down to C4:C24 with the fill handle (the little square in the lower right corner of the cell). NewList will contain all elements from OldList except duplicates and empty cells. The rest of the range is filled with #VALUE! NewList will be used as your new validation list. Enter this formula in the validation source field: =OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0)) The validation box will now contain, what you were looking for. -- Best regards Leo Heuser Followup to newsgroup only please. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring Blank value in a drop down list
Hi Leo,
It worked just perfectly. Many thanks to you. Shail Leo Heuser wrote: "shail" skrev i en meddelelse oups.com... Hi, I have a dynamic drop down validation List, which comes from another spreadsheet list and have a few random blank cells in it. I need to drop these blank values from the List without making any change in the parent list. I may also have some duplicate values in the List and need to drop them too. Please help. Thanks, Shail Hi Shail Here's one way to do it: Assumptions: Name of the range of your current validation list: OldList 1. Make a new list (named NewList), with a number of cells which will never be reached in ordinary use. I have made the list in C3:C24. 2. C2 must be empty or must not contain data found in OldList. The list cannot start in row 1 with the formula used. 3. In C3 enter this array formula: =INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1) The formula must be entered with <Shift<Ctrl<Enter, also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed in braces {}. Don't enter these yourself. It's Excel's way of telling, that the formula is an array formula. 4. Copy C3 down to C4:C24 with the fill handle (the little square in the lower right corner of the cell). NewList will contain all elements from OldList except duplicates and empty cells. The rest of the range is filled with #VALUE! NewList will be used as your new validation list. Enter this formula in the validation source field: =OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0)) The validation box will now contain, what you were looking for. -- Best regards Leo Heuser Followup to newsgroup only please. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring Blank value in a drop down list
You're welcome, Shail, and thanks for the feedback :-)
Leo Heuser "shail" skrev i en meddelelse oups.com... Hi Leo, It worked just perfectly. Many thanks to you. Shail Leo Heuser wrote: "shail" skrev i en meddelelse oups.com... Hi, I have a dynamic drop down validation List, which comes from another spreadsheet list and have a few random blank cells in it. I need to drop these blank values from the List without making any change in the parent list. I may also have some duplicate values in the List and need to drop them too. Please help. Thanks, Shail Hi Shail Here's one way to do it: Assumptions: Name of the range of your current validation list: OldList 1. Make a new list (named NewList), with a number of cells which will never be reached in ordinary use. I have made the list in C3:C24. 2. C2 must be empty or must not contain data found in OldList. The list cannot start in row 1 with the formula used. 3. In C3 enter this array formula: =INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1) The formula must be entered with <Shift<Ctrl<Enter, also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed in braces {}. Don't enter these yourself. It's Excel's way of telling, that the formula is an array formula. 4. Copy C3 down to C4:C24 with the fill handle (the little square in the lower right corner of the cell). NewList will contain all elements from OldList except duplicates and empty cells. The rest of the range is filled with #VALUE! NewList will be used as your new validation list. Enter this formula in the validation source field: =OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0)) The validation box will now contain, what you were looking for. -- Best regards Leo Heuser Followup to newsgroup only please. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignoring Blank value in a drop down list
Hi Leo, That was a great technique. Could the dropdown listed in alphabetical order? Thanks in advance, Ricky Leo Heuser Wrote: You're welcome, Shail, and thanks for the feedback :-) Leo Heuser "shail" skrev i en meddelelse oups.com... Hi Leo, It worked just perfectly. Many thanks to you. Shail Leo Heuser wrote: "shail" skrev i en meddelelse oups.com... Hi, I have a dynamic drop down validation List, which comes from another spreadsheet list and have a few random blank cells in it. I need to drop these blank values from the List without making any change in the parent list. I may also have some duplicate values in the List and need to drop them too. Please help. Thanks, Shail Hi Shail Here's one way to do it: Assumptions: Name of the range of your current validation list: OldList 1. Make a new list (named NewList), with a number of cells which will never be reached in ordinary use. I have made the list in C3:C24. 2. C2 must be empty or must not contain data found in OldList. The list cannot start in row 1 with the formula used. 3. In C3 enter this array formula: =INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1) The formula must be entered with <Shift<Ctrl<Enter, also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed in braces {}. Don't enter these yourself. It's Excel's way of telling, that the formula is an array formula. 4. Copy C3 down to C4:C24 with the fill handle (the little square in the lower right corner of the cell). NewList will contain all elements from OldList except duplicates and empty cells. The rest of the range is filled with #VALUE! NewList will be used as your new validation list. Enter this formula in the validation source field: =OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0)) The validation box will now contain, what you were looking for. -- Best regards Leo Heuser Followup to newsgroup only please. -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=564718 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
WITH SORTED LIST. Ignoring Blank value in a drop down list.
Hi Ricky
I have made a complete description including how to make the list sorted with formulae only. Please see below. "ExcelQuestion" skrev i en meddelelse news:ExcelQuestion.2cjut6_1155591912.4299@excelfor um-nospam.com... Hi Leo, That was a great technique. Could the dropdown listed in alphabetical order? Thanks in advance, Ricky Leo Heuser wrote: "shail" skrev i en meddelelse oups.com... Hi, I have a dynamic drop down validation List, which comes from another spreadsheet list and have a few random blank cells in it. I need to drop these blank values from the List without making any change in the parent list. I may also have some duplicate values in the List and need to drop them too. Please help. Thanks, Shail Here's one way to do it: Assumptions: Name of the range of your current validation list: OldList 1. Make a new list (named NewList), with a number of cells, which will never be reached under normal conditions. I have made the list in C3:C24. 2. C2 must be empty or must not contain data found in OldList. The list cannot start in row 1 with the formula used. 3. In C3 enter this array formula: =INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1) The formula must be entered with <Shift<Ctrl<Enter, also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed in braces {}. Don't enter these yourself. It's Excel's way of telling, that the formula is an array formula. 4. Copy C3 down to C4:C24 with the fill handle (the little square in the lower right corner of the cell). NewList contains all elements from OldList except duplicates and empty cells. The rest of the range is filled with #VALUE! To get the new list in alphabetical order use this setup: 5. Make another list (named SortList), which has the same size as NewList. I have made it in E3:E24. 6. In E3 enter this array formula: =INDEX(NewList,MATCH(LARGE(MMULT(IF(ISERROR(NewLis t<=TRANSPOSE(NewList)),0,IF(NewList<=TRANSPOSE(New List),1,0)),ROW(NewList)^0),ROW()-ROW($E$3)+1),MMULT(IF(ISERROR(NewList<=TRANSPOSE(N ewList)),0,IF(NewList<=TRANSPOSE(NewList),1,0)),RO W(NewList)^0),0)) The formula must be entered with <Shift<Ctrl<Enter 7. Copy E3 down to E4:E24 with the fill handle. SortList contains NewList sorted. To use NewList as validation source enter this formula in the validation source field: =OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0)) To use SortList as validation source enter this formula in the validation source field: =OFFSET(SortList,,,SUM(NOT(ISERROR(SortList))+0)) Regards Leo Heuser |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Drop down list from another drop down list | Excel Discussion (Misc queries) | |||
| Advanced Excel Drop down List | Excel Discussion (Misc queries) | |||
| Populating worksheet via a drop down list ! | Excel Worksheet Functions | |||
| Limit drop down list and linking to other info | Excel Worksheet Functions | |||
| Summarize Out of Stock List | Excel Worksheet Functions | |||
Hybrid Mode
