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