Please help! I'm using a simple data validation to allow for drop down values
to select from. To allow for future addition of values, I've included a
number of blank cells (with ignore blank setting). Whenever the cell is
dropped down, the focus is on a blank cell at the bottom of my list of
values, and I then need to scroll up to see the values. Can anybody help with
this? Thanks.

Wilhelm,

Here's a self-adjusting way to get your dropdown. It's based on the dynamic range
technique. If your list starts in E1, and doesn't go past E100 (adjust if necessary), the
dropdown will list only the items it finds in E1 and down. The list in Column E mustn't have
any empty cells inside it. Use this in the Data Validation - List Source box:

=OFFSET(E1,0,0,COUNTA(E1:E100),1)

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Wilhelm" wrote in message
...
Please help! I'm using a simple data validation to allow for drop down values
to select from. To allow for future addition of values, I've included a
number of blank cells (with ignore blank setting). Whenever the cell is
dropped down, the focus is on a blank cell at the bottom of my list of
values, and I then need to scroll up to see the values. Can anybody help with
this? Thanks.

You have mis-interpreted the meaning of "ignore blank".

That just means if you are using a named range for the list and have "ignore
blank" checkmarked, you will be able to enter anything in the cell.

Does not mean to "hide the blank cells in the list".

Rather than leave a bunch of blank cells for future use, you should create a
dynamic range which will grow/shrink as more is added or removed.

See Debta Dlagleish's site for more on Dynamic Ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Mon, 3 Mar 2008 20:21:01 -0800, Wilhelm
wrote:

Please help! I'm using a simple data validation to allow for drop down values
to select from. To allow for future addition of values, I've included a
number of blank cells (with ignore blank setting). Whenever the cell is
dropped down, the focus is on a blank cell at the bottom of my list of
values, and I then need to scroll up to see the values. Can anybody help with
this? Thanks.

Thanks a stack, Earl!

"Earl Kiosterud" wrote:

Wilhelm,

Here's a self-adjusting way to get your dropdown. It's based on the dynamic range
technique. If your list starts in E1, and doesn't go past E100 (adjust if necessary), the
dropdown will list only the items it finds in E1 and down. The list in Column E mustn't have
any empty cells inside it. Use this in the Data Validation - List Source box:

=OFFSET(E1,0,0,COUNTA(E1:E100),1)

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Wilhelm" wrote in message
...
Please help! I'm using a simple data validation to allow for drop down values
to select from. To allow for future addition of values, I've included a
number of blank cells (with ignore blank setting). Whenever the cell is
dropped down, the focus is on a blank cell at the bottom of my list of
values, and I then need to scroll up to see the values. Can anybody help with
this? Thanks.

Thank you very much, Gord!

"Gord Dibben" wrote:

You have mis-interpreted the meaning of "ignore blank".

That just means if you are using a named range for the list and have "ignore
blank" checkmarked, you will be able to enter anything in the cell.

Does not mean to "hide the blank cells in the list".

Rather than leave a bunch of blank cells for future use, you should create a
dynamic range which will grow/shrink as more is added or removed.

See Debta Dlagleish's site for more on Dynamic Ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Mon, 3 Mar 2008 20:21:01 -0800, Wilhelm
wrote:

Please help! I'm using a simple data validation to allow for drop down values
to select from. To allow for future addition of values, I've included a
number of blank cells (with ignore blank setting). Whenever the cell is
dropped down, the focus is on a blank cell at the bottom of my list of
values, and I then need to scroll up to see the values. Can anybody help with
this? Thanks.

Thanks for the feedback.

I see Earl gave you a dynamic range formula to get you going.


GordI

On Mon, 3 Mar 2008 23:39:01 -0800, Wilhelm
wrote:

Thank you very much, Gord!

"Gord Dibben" wrote:

You have mis-interpreted the meaning of "ignore blank".

That just means if you are using a named range for the list and have "ignore
blank" checkmarked, you will be able to enter anything in the cell.

Does not mean to "hide the blank cells in the list".

Rather than leave a bunch of blank cells for future use, you should create a
dynamic range which will grow/shrink as more is added or removed.

See Debta Dlagleish's site for more on Dynamic Ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Mon, 3 Mar 2008 20:21:01 -0800, Wilhelm
wrote:

Please help! I'm using a simple data validation to allow for drop down values
to select from. To allow for future addition of values, I've included a
number of blank cells (with ignore blank setting). Whenever the cell is
dropped down, the focus is on a blank cell at the bottom of my list of
values, and I then need to scroll up to see the values. Can anybody help with
this? Thanks.