software

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

Instead of including blank cells in the name range, you could use dynamic
ranges, as described he

www.contextures.com/xlNames01.html

Or dependent validation lists:

www.contextures.com/xlDataVal02.html

--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlquot;Hayleyquot; gt; wrote in message
...
gt; I have 2 validation boxes. The first is a simple list of 5 options (based
on
gt; a 'name' range). The second is a filter of a larger list depending on the
gt; answer in the first box. I have made my 'name' range as big as the largest
gt; list but other lists are quite short. The problem I have it that the drop
gt; down box is showing all the blank cells in my 'name' range.
gt;
gt; The bigger problem is that when you select the pull down it highlights the
gt; blank field first instead of the first item on the list so you have to
scroll
gt; up. This is a problem as staff think there is nothing on the list. So how
do
gt; I either not show the blanks or get the drop down to start at the top of
the
gt; list?
gt; --
gt; Hayley
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.
--
HayleyHi!

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

quot;Hayleyquot; gt; wrote in message
...
gt; The range is an array formula so I had copy it down enough rows to allow
gt; for
gt; the largest number of items. So while the result is blank the cell has a
gt; formula. So your suggestion didn't work.
gt; --
gt; Hayley
gt;
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)),quot;quot;,INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2))
--
Hayleyquot;Biffquot; wrote:

gt; Hi!
gt;
gt; Are the blanks cells scattered within the range or are they all at the end
gt; of the range?
gt;
gt; Did you try Debra's suggestion of using a dynamic range? If so, what does
gt; you formula look like?
gt;
gt; Biff
gt;
gt;

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 = quot;quot; (formula blank)
A7 = quot;quot; (formula blank)
A8 = quot;quot; (formula blank)
A9 = quot;quot; (formula blank)
A10 = quot;quot; (formula blank)

To use this range as the source for a drop down:

Select the cell where you want the drop down.
Goto Datagt;Validation
Allow: List
Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)gt;0)))
OK out

Your drop down list will not contain any blank selections.

gt; =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),quot;quot;,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)lt;=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))),quot;quot;)

Biff

quot;Hayleyquot; gt; wrote in message
...
gt; Blank cells are at the end of the range.
gt;
gt; The formula in the 'name' range was the same as Debra's suggestion (with
gt; columns changed). The formula in the select cell is as below
gt;
gt; =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),quot;quot;,INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2))
gt; --
gt; Hayley
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Are the blanks cells scattered within the range or are they all at the
gt;gt; end
gt;gt; of the range?
gt;gt;
gt;gt; Did you try Debra's suggestion of using a dynamic range? If so, what does
gt;gt; you formula look like?
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt;
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
office.microsoft.com/en-us/as...260381033.aspx

--
Thanks again, much appreciated. Hayleyquot;Biffquot; wrote:

gt; That's the formula you are using as the source for your drop down list?
gt;
gt; That formula only returns a single value.
gt;
gt; I think we're not understanding each other!
gt;
gt; Assume you have a range of values that you want to use as the source for a
gt; data validation drop down list. That range includes formula blanks in
gt; several cells at the end of the range. This range is A1:A10 and looks
gt; something like this:
gt;
gt; A1 = Joe
gt; A2 = Mary
gt; A3 = Lisa
gt; A4 = Tom
gt; A5 = Karen
gt; A6 = quot;quot; (formula blank)
gt; A7 = quot;quot; (formula blank)
gt; A8 = quot;quot; (formula blank)
gt; A9 = quot;quot; (formula blank)
gt; A10 = quot;quot; (formula blank)
gt;
gt; To use this range as the source for a drop down:
gt;
gt; Select the cell where you want the drop down.
gt; Goto Datagt;Validation
gt; Allow: List
gt; Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)gt;0)))
gt; OK out
gt;
gt; Your drop down list will not contain any blank selections.
gt;
gt; gt; =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),quot;quot;,INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2))
gt;
gt; While I'm at it, that formula can be shortened to: (if my hunch is correct!)
gt;
gt; =IF(ROWS($1:1)lt;=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))),quot;quot;)
gt;
gt; Biff
gt;
gt; quot;Hayleyquot; gt; wrote in message
gt; ...
gt; gt; Blank cells are at the end of the range.
gt; gt;
gt; gt; The formula in the 'name' range was the same as Debra's suggestion (with
gt; gt; columns changed). The formula in the select cell is as below
gt; gt;
gt; gt; =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),quot;quot;,INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2))
gt; gt; --
gt; gt; Hayley
gt; gt;
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt; Are the blanks cells scattered within the range or are they all at the
gt; gt;gt; end
gt; gt;gt; of the range?
gt; gt;gt;
gt; gt;gt; Did you try Debra's suggestion of using a dynamic range? If so, what does
gt; gt;gt; you formula look like?
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

If you name your list you can use it in another sheet, insertgt;namegt;define
and give it a name like MyList
then refer to it as

=MyList

in the source box

--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;Hayleyquot; gt; wrote in message
...
gt; Thanks Biff!!!!!!!!!
gt;
gt; Works a treat when i put it in the source field. I was trying to put it in
gt; the name field and wasn't working. However my reference list is on another
gt; sheet so validation dosn't allow this however I can lock down the first
gt; sheet
gt; and hid the list there.
gt;
gt; Your shortened formaula also works. I have NO idea what it all means but
gt; it
gt; works. I found the first one at
gt; office.microsoft.com/en-us/as...260381033.aspx
gt;
gt; --
gt; Thanks again, much appreciated. Hayley
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; That's the formula you are using as the source for your drop down list?
gt;gt;
gt;gt; That formula only returns a single value.
gt;gt;
gt;gt; I think we're not understanding each other!
gt;gt;
gt;gt; Assume you have a range of values that you want to use as the source for
gt;gt; a
gt;gt; data validation drop down list. That range includes formula blanks in
gt;gt; several cells at the end of the range. This range is A1:A10 and looks
gt;gt; something like this:
gt;gt;
gt;gt; A1 = Joe
gt;gt; A2 = Mary
gt;gt; A3 = Lisa
gt;gt; A4 = Tom
gt;gt; A5 = Karen
gt;gt; A6 = quot;quot; (formula blank)
gt;gt; A7 = quot;quot; (formula blank)
gt;gt; A8 = quot;quot; (formula blank)
gt;gt; A9 = quot;quot; (formula blank)
gt;gt; A10 = quot;quot; (formula blank)
gt;gt;
gt;gt; To use this range as the source for a drop down:
gt;gt;
gt;gt; Select the cell where you want the drop down.
gt;gt; Goto Datagt;Validation
gt;gt; Allow: List
gt;gt; Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)gt;0)))
gt;gt; OK out
gt;gt;
gt;gt; Your drop down list will not contain any blank selections.
gt;gt;
gt;gt; gt; =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),quot;quot;,INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2))
gt;gt;
gt;gt; While I'm at it, that formula can be shortened to: (if my hunch is
gt;gt; correct!)
gt;gt;
gt;gt; =IF(ROWS($1:1)lt;=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))),quot;quot;)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Hayleyquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Blank cells are at the end of the range.
gt;gt; gt;
gt;gt; gt; The formula in the 'name' range was the same as Debra's suggestion
gt;gt; gt; (with
gt;gt; gt; columns changed). The formula in the select cell is as below
gt;gt; gt;
gt;gt; gt; =IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),quot;quot;,INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2))
gt;gt; gt; --
gt;gt; gt; Hayley
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi!
gt;gt; gt;gt;
gt;gt; gt;gt; Are the blanks cells scattered within the range or are they all at the
gt;gt; gt;gt; end
gt;gt; gt;gt; of the range?
gt;gt; gt;gt;
gt;gt; gt;gt; Did you try Debra's suggestion of using a dynamic range? If so, what
gt;gt; gt;gt; does
gt;gt; gt;gt; you formula look like?
gt;gt; gt;gt;
gt;gt; gt;gt; Biff
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
So how would Biff formula work/look??
gt;gt; Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)gt;0)))

--
Hayleyquot;Peo Sjoblomquot; wrote:

gt; If you name your list you can use it in another sheet, insertgt;namegt;define
gt; and give it a name like MyList
gt; then refer to it as
gt;
gt; =MyList
gt;
gt; in the source box
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;

Let's assume the list is on sheet 2 A1:A10:

A1 = Joe
A2 = Mary
A3 = Lisa
A4 = Tom
A5 = Karen
A6 = quot;quot; (formula blank)
A7 = quot;quot; (formula blank)
A8 = quot;quot; (formula blank)
A9 = quot;quot; (formula blank)
A10 = quot;quot; (formula blank)

gt; So how would Biff formula work/look??
gt;gt;gt; Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)gt;0)))

Goto Insertgt;Namegt;Define
Names in workbook: type in: Mylist
Refers to: =OFFSET(Sheet2!$A$1,,,SUMPRODUCT(--(LEN(Sheet2!$A$1:$A$10)gt;0)))
OK

Now, back on Sheet1 (or whatever other sheet) where your drop down is, as
the source for the drop down use =MyList.

gt;Your shortened formaula also works. I have NO idea what it all means but it
gt;works. I found the first one at
gt;office.microsoft.com/en-us/as...260381033.aspx

Yes, I've seen that article. I quot;usedquot; to write formulas like that too! lt;bggt;

The =IF(ROWS($1:1)lt;=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

quot;Hayleyquot; gt; wrote in message
...
gt; So how would Biff formula work/look??
gt;gt;gt; Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)gt;0)))
gt;
gt; --
gt; Hayley
gt;
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; If you name your list you can use it in another sheet, insertgt;namegt;define
gt;gt; and give it a name like MyList
gt;gt; then refer to it as
gt;gt;
gt;gt; =MyList
gt;gt;
gt;gt; in the source box
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt;