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