data validation
 

I have created a data validation list that pulls values from cells E98:E112.
E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is
copied down through E112. Only cells E98:E105 have values in them. When the
dropdown for the data validation list is clicked, the scroll bar is at the
very bottom of the list displaying empty values and I always need to scroll
to the top of the list. Why is the top of the list not automatically
displayed in the dropdown rather than the bottom of the list?
--
maryj

data validation
 

Your current data validation list contains blanks, and when the Data
Validation dropdown is opened, it finds a match to the current content
of the cell. It selects the first blank in the dropdown list, because it
is a match for the blank in the cell. To prevent this, you could fill
the cell with the first item from the list, as a default value.

Or, you can use a named dynamic range as the source. There are
instructions he

http://www.contextures.com/xlNames01.html

Then, in the Data Validation Source box, type an equal sign and the name
of the range, e.g.: =MyRange


maryj wrote:
I have created a data validation list that pulls values from cells E98:E112.
E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is
copied down through E112. Only cells E98:E105 have values in them. When the
dropdown for the data validation list is clicked, the scroll bar is at the
very bottom of the list displaying empty values and I always need to scroll
to the top of the list. Why is the top of the list not automatically
displayed in the dropdown rather than the bottom of the list?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

data validation
 

Thanks Debra. I tried to create a dynamic range using the offset and the
counta, except the counta doesn't work since Excel does not interpret the
cells as being blank since they contain a formula. I'd love to hear any
other suggestions you have.
--
maryj


"Debra Dalgleish" wrote:

Your current data validation list contains blanks, and when the Data
Validation dropdown is opened, it finds a match to the current content
of the cell. It selects the first blank in the dropdown list, because it
is a match for the blank in the cell. To prevent this, you could fill
the cell with the first item from the list, as a default value.

Or, you can use a named dynamic range as the source. There are
instructions he

http://www.contextures.com/xlNames01.html

Then, in the Data Validation Source box, type an equal sign and the name
of the range, e.g.: =MyRange


maryj wrote:
I have created a data validation list that pulls values from cells E98:E112.
E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is
copied down through E112. Only cells E98:E105 have values in them. When the
dropdown for the data validation list is clicked, the scroll bar is at the
very bottom of the list displaying empty values and I always need to scroll
to the top of the list. Why is the top of the list not automatically
displayed in the dropdown rather than the bottom of the list?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

data validation
 

Instead of using COUNTA in the formula, you could use COUNTIF, e.g.:

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"0") ,1)

maryj wrote:
Thanks Debra. I tried to create a dynamic range using the offset and the
counta, except the counta doesn't work since Excel does not interpret the
cells as being blank since they contain a formula. I'd love to hear any
other suggestions you have.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

data validation
 

I use this technique to limit the print area when I have formulas that evaluate
to "".

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$2000<""),ROW(Sheet1!$A$1:$A$2000) )

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5)

That last 5 represents the last column to print (A:E).

And change the worksheet (sheet1) if necessary (in all the places).

===
It works nicely when there are "" intermingled in the column. (Maybe not too
nice a fit for you???)

maryj wrote:

Thanks Debra. I tried to create a dynamic range using the offset and the
counta, except the counta doesn't work since Excel does not interpret the
cells as being blank since they contain a formula. I'd love to hear any
other suggestions you have.
--
maryj

"Debra Dalgleish" wrote:

Your current data validation list contains blanks, and when the Data
Validation dropdown is opened, it finds a match to the current content
of the cell. It selects the first blank in the dropdown list, because it
is a match for the blank in the cell. To prevent this, you could fill
the cell with the first item from the list, as a default value.

Or, you can use a named dynamic range as the source. There are
instructions he

http://www.contextures.com/xlNames01.html

Then, in the Data Validation Source box, type an equal sign and the name
of the range, e.g.: =MyRange


maryj wrote:
I have created a data validation list that pulls values from cells E98:E112.
E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is
copied down through E112. Only cells E98:E105 have values in them. When the
dropdown for the data validation list is clicked, the scroll bar is at the
very bottom of the list displaying empty values and I always need to scroll
to the top of the list. Why is the top of the list not automatically
displayed in the dropdown rather than the bottom of the list?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



--

Dave Peterson

data validation
 

Thanks Dave and Debra for your great suggestions. You guys are the best!!
What we ended up doing was rather than using the cells with the formulas
linking to the other sheet, we created a dynamic range on sheet where the
actual values were entered. This eliminated the issue of needing to use "".
--
maryj


"Debra Dalgleish" wrote:

Instead of using COUNTA in the formula, you could use COUNTIF, e.g.:

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"0") ,1)

maryj wrote:
Thanks Debra. I tried to create a dynamic range using the offset and the
counta, except the counta doesn't work since Excel does not interpret the
cells as being blank since they contain a formula. I'd love to hear any
other suggestions you have.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

data validation
 

Dave, How would you do this with a fixed number of rows but dynamic COLUMNS?

"Dave Peterson" wrote:

I use this technique to limit the print area when I have formulas that evaluate
to "".

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$2000<""),ROW(Sheet1!$A$1:$A$2000) )

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5)

That last 5 represents the last column to print (A:E).

And change the worksheet (sheet1) if necessary (in all the places).

===
It works nicely when there are "" intermingled in the column. (Maybe not too
nice a fit for you???)

maryj wrote:

Thanks Debra. I tried to create a dynamic range using the offset and the
counta, except the counta doesn't work since Excel does not interpret the
cells as being blank since they contain a formula. I'd love to hear any
other suggestions you have.
--
maryj

"Debra Dalgleish" wrote:

Your current data validation list contains blanks, and when the Data
Validation dropdown is opened, it finds a match to the current content
of the cell. It selects the first blank in the dropdown list, because it
is a match for the blank in the cell. To prevent this, you could fill
the cell with the first item from the list, as a default value.

Or, you can use a named dynamic range as the source. There are
instructions he

http://www.contextures.com/xlNames01.html

Then, in the Data Validation Source box, type an equal sign and the name
of the range, e.g.: =MyRange


maryj wrote:
I have created a data validation list that pulls values from cells E98:E112.
E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this formula is
copied down through E112. Only cells E98:E105 have values in them. When the
dropdown for the data validation list is clicked, the scroll bar is at the
very bottom of the list displaying empty values and I always need to scroll
to the top of the list. Why is the top of the list not automatically
displayed in the dropdown rather than the bottom of the list?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



--

Dave Peterson

data validation
 

Go across and the use COLUMN instead of ROW

=LOOKUP(2,1/(Sheet1!$1:$1<""),COLUMN(Sheet1!$1:$1))

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"GeorgeW" wrote in message
...
Dave, How would you do this with a fixed number of rows but dynamic
COLUMNS?

"Dave Peterson" wrote:

I use this technique to limit the print area when I have formulas that
evaluate
to "".

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to:
=LOOKUP(2,1/(Sheet1!$A$1:$A$2000<""),ROW(Sheet1!$A$1:$A$2000) )

(Make that 2000 big enough to extend past the last possible row--but
don't use
the whole column.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5)

That last 5 represents the last column to print (A:E).

And change the worksheet (sheet1) if necessary (in all the places).

===
It works nicely when there are "" intermingled in the column. (Maybe not
too
nice a fit for you???)

maryj wrote:

Thanks Debra. I tried to create a dynamic range using the offset and
the
counta, except the counta doesn't work since Excel does not interpret
the
cells as being blank since they contain a formula. I'd love to hear
any
other suggestions you have.
--
maryj

"Debra Dalgleish" wrote:

Your current data validation list contains blanks, and when the Data
Validation dropdown is opened, it finds a match to the current
content
of the cell. It selects the first blank in the dropdown list, because
it
is a match for the blank in the cell. To prevent this, you could fill
the cell with the first item from the list, as a default value.

Or, you can use a named dynamic range as the source. There are
instructions he

http://www.contextures.com/xlNames01.html

Then, in the Data Validation Source box, type an equal sign and the
name
of the range, e.g.: =MyRange


maryj wrote:
I have created a data validation list that pulls values from cells
E98:E112.
E98 has the following formula: =IF('2'!B8="","",'2'!B8) and this
formula is
copied down through E112. Only cells E98:E105 have values in them.
When the
dropdown for the data validation list is clicked, the scroll bar is
at the
very bottom of the list displaying empty values and I always need
to scroll
to the top of the list. Why is the top of the list not
automatically
displayed in the dropdown rather than the bottom of the list?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



--

Dave Peterson