| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation - clear blanks from drop down
I have 2 validation boxes. The first is a simple list of 5 options (based on
a 'name' range). The second is a filter of a larger list depending on the answer in the first box. I have made my 'name' range as big as the largest list but other lists are quite short. The problem I have it that the drop down box is showing all the blank cells in my 'name' range. The bigger problem is that when you select the pull down it highlights the blank field first instead of the first item on the list so you have to scroll up. This is a problem as staff think there is nothing on the list. So how do I either not show the blanks or get the drop down to start at the top of the list? -- Hayley |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
Instead of including blank cells in the name range, you could use dynamic
ranges, as described he http://www.contextures.com/xlNames01.html Or dependent validation lists: http://www.contextures.com/xlDataVal02.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html "Hayley" wrote in message ... I have 2 validation boxes. The first is a simple list of 5 options (based on a 'name' range). The second is a filter of a larger list depending on the answer in the first box. I have made my 'name' range as big as the largest list but other lists are quite short. The problem I have it that the drop down box is showing all the blank cells in my 'name' range. The bigger problem is that when you select the pull down it highlights the blank field first instead of the first item on the list so you have to scroll up. This is a problem as staff think there is nothing on the list. So how do I either not show the blanks or get the drop down to start at the top of the list? -- Hayley |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
The range is an array formula so I had copy it down enough rows to allow for
the largest number of items. So while the result is blank the cell has a formula. So your suggestion didn't work. -- Hayley |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
Hi!
Are the blanks cells scattered within the range or are they all at the end of the range? Did you try Debra's suggestion of using a dynamic range? If so, what does you formula look like? Biff "Hayley" wrote in message ... The range is an array formula so I had copy it down enough rows to allow for the largest number of items. So while the result is blank the cell has a formula. So your suggestion didn't work. -- Hayley |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
Blank cells are at the end of the range.
The formula in the 'name' range was the same as Debra's suggestion (with columns changed). The formula in the select cell is as below =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) -- Hayley "Biff" wrote: Hi! Are the blanks cells scattered within the range or are they all at the end of the range? Did you try Debra's suggestion of using a dynamic range? If so, what does you formula look like? Biff |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
That's the formula you are using as the source for your drop down list?
That formula only returns a single value. I think we're not understanding each other! Assume you have a range of values that you want to use as the source for a data validation drop down list. That range includes formula blanks in several cells at the end of the range. This range is A1:A10 and looks something like this: A1 = Joe A2 = Mary A3 = Lisa A4 = Tom A5 = Karen A6 = "" (formula blank) A7 = "" (formula blank) A8 = "" (formula blank) A9 = "" (formula blank) A10 = "" (formula blank) To use this range as the source for a drop down: Select the cell where you want the drop down. Goto DataValidation Allow: List Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0))) OK out Your drop down list will not contain any blank selections. =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) While I'm at it, that formula can be shortened to: (if my hunch is correct!) =IF(ROWS($1:1)<=COUNTIF($T$1:$T$62,$W$1),INDEX($U$ 1:$U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)), ROW(3:3))),"") Biff "Hayley" wrote in message ... Blank cells are at the end of the range. The formula in the 'name' range was the same as Debra's suggestion (with columns changed). The formula in the select cell is as below =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) -- Hayley "Biff" wrote: Hi! Are the blanks cells scattered within the range or are they all at the end of the range? Did you try Debra's suggestion of using a dynamic range? If so, what does you formula look like? Biff |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
Thanks Biff!!!!!!!!!
Works a treat when i put it in the source field. I was trying to put it in the name field and wasn't working. However my reference list is on another sheet so validation dosn't allow this however I can lock down the first sheet and hid the list there. Your shortened formaula also works. I have NO idea what it all means but it works. I found the first one at http://office.microsoft.com/en-us/as...260381033.aspx -- Thanks again, much appreciated. Hayley "Biff" wrote: That's the formula you are using as the source for your drop down list? That formula only returns a single value. I think we're not understanding each other! Assume you have a range of values that you want to use as the source for a data validation drop down list. That range includes formula blanks in several cells at the end of the range. This range is A1:A10 and looks something like this: A1 = Joe A2 = Mary A3 = Lisa A4 = Tom A5 = Karen A6 = "" (formula blank) A7 = "" (formula blank) A8 = "" (formula blank) A9 = "" (formula blank) A10 = "" (formula blank) To use this range as the source for a drop down: Select the cell where you want the drop down. Goto DataValidation Allow: List Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0))) OK out Your drop down list will not contain any blank selections. =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) While I'm at it, that formula can be shortened to: (if my hunch is correct!) =IF(ROWS($1:1)<=COUNTIF($T$1:$T$62,$W$1),INDEX($U$ 1:$U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)), ROW(3:3))),"") Biff "Hayley" wrote in message ... Blank cells are at the end of the range. The formula in the 'name' range was the same as Debra's suggestion (with columns changed). The formula in the select cell is as below =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) -- Hayley "Biff" wrote: Hi! Are the blanks cells scattered within the range or are they all at the end of the range? Did you try Debra's suggestion of using a dynamic range? If so, what does you formula look like? Biff |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
If you name your list you can use it in another sheet, insertnamedefine
and give it a name like MyList then refer to it as =MyList in the source box -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Hayley" wrote in message ... Thanks Biff!!!!!!!!! Works a treat when i put it in the source field. I was trying to put it in the name field and wasn't working. However my reference list is on another sheet so validation dosn't allow this however I can lock down the first sheet and hid the list there. Your shortened formaula also works. I have NO idea what it all means but it works. I found the first one at http://office.microsoft.com/en-us/as...260381033.aspx -- Thanks again, much appreciated. Hayley "Biff" wrote: That's the formula you are using as the source for your drop down list? That formula only returns a single value. I think we're not understanding each other! Assume you have a range of values that you want to use as the source for a data validation drop down list. That range includes formula blanks in several cells at the end of the range. This range is A1:A10 and looks something like this: A1 = Joe A2 = Mary A3 = Lisa A4 = Tom A5 = Karen A6 = "" (formula blank) A7 = "" (formula blank) A8 = "" (formula blank) A9 = "" (formula blank) A10 = "" (formula blank) To use this range as the source for a drop down: Select the cell where you want the drop down. Goto DataValidation Allow: List Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0))) OK out Your drop down list will not contain any blank selections. =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) While I'm at it, that formula can be shortened to: (if my hunch is correct!) =IF(ROWS($1:1)<=COUNTIF($T$1:$T$62,$W$1),INDEX($U$ 1:$U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)), ROW(3:3))),"") Biff "Hayley" wrote in message ... Blank cells are at the end of the range. The formula in the 'name' range was the same as Debra's suggestion (with columns changed). The formula in the select cell is as below =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) -- Hayley "Biff" wrote: Hi! Are the blanks cells scattered within the range or are they all at the end of the range? Did you try Debra's suggestion of using a dynamic range? If so, what does you formula look like? Biff |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
So how would Biff formula work/look??
Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0))) -- Hayley "Peo Sjoblom" wrote: If you name your list you can use it in another sheet, insertnamedefine and give it a name like MyList then refer to it as =MyList in the source box -- Regards, Peo Sjoblom http://nwexcelsolutions.com |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
Let's assume the list is on sheet 2 A1:A10:
A1 = Joe A2 = Mary A3 = Lisa A4 = Tom A5 = Karen A6 = "" (formula blank) A7 = "" (formula blank) A8 = "" (formula blank) A9 = "" (formula blank) A10 = "" (formula blank) So how would Biff formula work/look?? Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0))) Goto InsertNameDefine Names in workbook: type in: Mylist Refers to: =OFFSET(Sheet2!$A$1,,,SUMPRODUCT(--(LEN(Sheet2!$A$1:$A$10)0))) OK Now, back on Sheet1 (or whatever other sheet) where your drop down is, as the source for the drop down use =MyList. Your shortened formaula also works. I have NO idea what it all means but it works. I found the first one at http://office.microsoft.com/en-us/as...260381033.aspx Yes, I've seen that article. I "used" to write formulas like that too! <bg The =IF(ROWS($1:1)<=COUNTIF($T$1:$T$62,$W$1) part is just a pseudo error trap and is much shorter and more efficient than: =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)) Biff "Hayley" wrote in message ... So how would Biff formula work/look?? Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0))) -- Hayley "Peo Sjoblom" wrote: If you name your list you can use it in another sheet, insertnamedefine and give it a name like MyList then refer to it as =MyList in the source box -- Regards, Peo Sjoblom http://nwexcelsolutions.com |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
EXCELLENT - All works - phew! Thanks for your help. My challenge now is to
try and understand what the formulas are all doing! -- Hayley |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
Biff,
What if you data has blanks at the beginning of the data? How do you prevent those blanks from showing up? Great stuff by the way! David "Biff" wrote: That's the formula you are using as the source for your drop down list? That formula only returns a single value. I think we're not understanding each other! Assume you have a range of values that you want to use as the source for a data validation drop down list. That range includes formula blanks in several cells at the end of the range. This range is A1:A10 and looks something like this: A1 = Joe A2 = Mary A3 = Lisa A4 = Tom A5 = Karen A6 = "" (formula blank) A7 = "" (formula blank) A8 = "" (formula blank) A9 = "" (formula blank) A10 = "" (formula blank) To use this range as the source for a drop down: Select the cell where you want the drop down. Goto DataValidation Allow: List Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0))) OK out Your drop down list will not contain any blank selections. =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) While I'm at it, that formula can be shortened to: (if my hunch is correct!) =IF(ROWS($1:1)<=COUNTIF($T$1:$T$62,$W$1),INDEX($U$ 1:$U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)), ROW(3:3))),"") Biff "Hayley" wrote in message ... Blank cells are at the end of the range. The formula in the 'name' range was the same as Debra's suggestion (with columns changed). The formula in the select cell is as below =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) -- Hayley "Biff" wrote: Hi! Are the blanks cells scattered within the range or are they all at the end of the range? Did you try Debra's suggestion of using a dynamic range? If so, what does you formula look like? Biff |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
See this screencap:
http://img107.imageshack.us/img107/5702/sourcenf2.jpg The drop down is in cell A1. The source for the drop down is F1:F7. If the blank cells at the top of the source list are *empty* cells you could use the formula in row 11 as the drop down source. Or, you could use the formula in row 13 which accounts for both empty cells and cells that are blank as a result of formula blanks. If the source list has a header followed by empty/blank cells: Header <empty <empty Bill Mary Sam Change the formula OFFSET to start at the first empty/blank cell. Biff "David" wrote in message ... Biff, What if you data has blanks at the beginning of the data? How do you prevent those blanks from showing up? Great stuff by the way! David "Biff" wrote: That's the formula you are using as the source for your drop down list? That formula only returns a single value. I think we're not understanding each other! Assume you have a range of values that you want to use as the source for a data validation drop down list. That range includes formula blanks in several cells at the end of the range. This range is A1:A10 and looks something like this: A1 = Joe A2 = Mary A3 = Lisa A4 = Tom A5 = Karen A6 = "" (formula blank) A7 = "" (formula blank) A8 = "" (formula blank) A9 = "" (formula blank) A10 = "" (formula blank) To use this range as the source for a drop down: Select the cell where you want the drop down. Goto DataValidation Allow: List Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0))) OK out Your drop down list will not contain any blank selections. =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) While I'm at it, that formula can be shortened to: (if my hunch is correct!) =IF(ROWS($1:1)<=COUNTIF($T$1:$T$62,$W$1),INDEX($U$ 1:$U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)), ROW(3:3))),"") Biff "Hayley" wrote in message ... Blank cells are at the end of the range. The formula in the 'name' range was the same as Debra's suggestion (with columns changed). The formula in the select cell is as below =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) -- Hayley "Biff" wrote: Hi! Are the blanks cells scattered within the range or are they all at the end of the range? Did you try Debra's suggestion of using a dynamic range? If so, what does you formula look like? Biff |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation - clear blanks from drop down
Need to clarify:
If the source list has a header followed by empty/blank cells: Change the formula OFFSET to start at the first empty/blank cell. What I meant was change *all* references in the formula to start with the first empty/blank cell. Biff "T. Valko" wrote in message ... See this screencap: http://img107.imageshack.us/img107/5702/sourcenf2.jpg The drop down is in cell A1. The source for the drop down is F1:F7. If the blank cells at the top of the source list are *empty* cells you could use the formula in row 11 as the drop down source. Or, you could use the formula in row 13 which accounts for both empty cells and cells that are blank as a result of formula blanks. If the source list has a header followed by empty/blank cells: Header <empty <empty Bill Mary Sam Change the formula OFFSET to start at the first empty/blank cell. Biff "David" wrote in message ... Biff, What if you data has blanks at the beginning of the data? How do you prevent those blanks from showing up? Great stuff by the way! David "Biff" wrote: That's the formula you are using as the source for your drop down list? That formula only returns a single value. I think we're not understanding each other! Assume you have a range of values that you want to use as the source for a data validation drop down list. That range includes formula blanks in several cells at the end of the range. This range is A1:A10 and looks something like this: A1 = Joe A2 = Mary A3 = Lisa A4 = Tom A5 = Karen A6 = "" (formula blank) A7 = "" (formula blank) A8 = "" (formula blank) A9 = "" (formula blank) A10 = "" (formula blank) To use this range as the source for a drop down: Select the cell where you want the drop down. Goto DataValidation Allow: List Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0))) OK out Your drop down list will not contain any blank selections. =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) While I'm at it, that formula can be shortened to: (if my hunch is correct!) =IF(ROWS($1:1)<=COUNTIF($T$1:$T$62,$W$1),INDEX($U$ 1:$U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)), ROW(3:3))),"") Biff "Hayley" wrote in message ... Blank cells are at the end of the range. The formula in the 'name' range was the same as Debra's suggestion (with columns changed). The formula in the select cell is as below =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2)) -- Hayley "Biff" wrote: Hi! Are the blanks cells scattered within the range or are they all at the end of the range? Did you try Debra's suggestion of using a dynamic range? If so, what does you formula look like? Biff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I have lost the in cell drop down in validation | Excel Worksheet Functions | |||
| Data Validation - ignore blanks | Excel Discussion (Misc queries) | |||
| Data Validation and Blanks in List | Excel Worksheet Functions | |||
| validation list drop down box, how do I bring in text commands | Excel Discussion (Misc queries) | |||
| drop downs & data validation | Excel Discussion (Misc queries) | |||
Linear Mode
