I have a column that contains empty cells and text. I want to return the
reference of the first cell in the column to contain text. For example

A2 = empty cell
A3 = empty cell
A4 = 2L

The formula would return A4

Thanks for your help

Input this as an array (use Ctrl+Shift+Enter to confirm):
=ADDRESS(MIN(IF(ISTEXT(A2:A4),ROW(A2:A4),FALSE)),C OLUMN(A2),4)


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

I have a column that contains empty cells and text. I want to return the
reference of the first cell in the column to contain text. For example

A2 = empty cell
A3 = empty cell
A4 = 2L

The formula would return A4

Thanks for your help

Thanks luke This works

Is it posible to explain how?

Thanks again

"Luke M" wrote:

Input this as an array (use Ctrl+Shift+Enter to confirm):
=ADDRESS(MIN(IF(ISTEXT(A2:A4),ROW(A2:A4),FALSE)),C OLUMN(A2),4)


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

I have a column that contains empty cells and text. I want to return the
reference of the first cell in the column to contain text. For example

A2 = empty cell
A3 = empty cell
A4 = 2L

The formula would return A4

Thanks for your help

Also why do you have to use ctrl+shift+enter? I notice that this add {} to
either end but can't find out why??

"Luke M" wrote:

Input this as an array (use Ctrl+Shift+Enter to confirm):
=ADDRESS(MIN(IF(ISTEXT(A2:A4),ROW(A2:A4),FALSE)),C OLUMN(A2),4)


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

I have a column that contains empty cells and text. I want to return the
reference of the first cell in the column to contain text. For example

A2 = empty cell
A3 = empty cell
A4 = 2L

The formula would return A4

Thanks for your help

Ctrl + Shift + Enter is how an array function is entered in to a workbook.
For a really clear and useful explanation of arrays I would suggest the
following web site maintained by Chip Pearson an Excel MS MVP:

http://www.cpearson.com/excel/ArrayFormulas.aspx

I would also recommend that you peruse the Table of Contents on this site as
it has a treasure trove of information and how-to articles.

Hope this helps.
--
Kevin Backmann


"M" wrote:

Also why do you have to use ctrl+shift+enter? I notice that this add {} to
either end but can't find out why??

"Luke M" wrote:

Input this as an array (use Ctrl+Shift+Enter to confirm):
=ADDRESS(MIN(IF(ISTEXT(A2:A4),ROW(A2:A4),FALSE)),C OLUMN(A2),4)


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

I have a column that contains empty cells and text. I want to return the
reference of the first cell in the column to contain text. For example

A2 = empty cell
A3 = empty cell
A4 = 2L

The formula would return A4

Thanks for your help

It looks like Luke has gone so I'll explain how the formula works

first consider the formula to be in 3 parts and I've put a | in to show the
division between those parts


=ADDRESS(MIN(IF(ISTEXT(A2:A4),ROW(A2:A4),FALSE))|, COLUMN(A2)|,4)

which translates as

address(find the row,find the column,type of reference)

find the row

IF(ISTEXT(A2:A4),ROW(A2:A4),FALSE)

because it is array entered using CTRL+Shift +Enter this part of the formula
examines very row in the range of 4 cells and returns an array of row numbers
IF the row contains text. If all the cells contained text it would return

2;3;4

if only rows 2 and 4 contained text it would return 2;FALSE;4

we asked the formula to return the MIN of these so we now have a 2 for the row

The next part find the column

COLUMN(A2)

A2 is in column 1 so we now have the column and the formula is now effectively

=address(2,1,4) - put that in a cell and see what it returns

the number 4 means relative so the formula returns A2

change the 4 to a 1 and it would return $A$4

Mike



"M" wrote:

Also why do you have to use ctrl+shift+enter? I notice that this add {} to
either end but can't find out why??

"Luke M" wrote:

Input this as an array (use Ctrl+Shift+Enter to confirm):
=ADDRESS(MIN(IF(ISTEXT(A2:A4),ROW(A2:A4),FALSE)),C OLUMN(A2),4)


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

I have a column that contains empty cells and text. I want to return the
reference of the first cell in the column to contain text. For example

A2 = empty cell
A3 = empty cell
A4 = 2L

The formula would return A4

Thanks for your help

I have a column that contains empty cells and text.

Try this:

=ADDRESS(MATCH("*",A:A,0),1,4)


--
Biff
Microsoft Excel MVP


"M" wrote:

I have a column that contains empty cells and text. I want to return the
reference of the first cell in the column to contain text. For example

A2 = empty cell
A3 = empty cell
A4 = 2L

The formula would return A4

Thanks for your help

On Thu, 26 Feb 2009 10:01:05 -0800, M wrote:

I have a column that contains empty cells and text. I want to return the
reference of the first cell in the column to contain text. For example

A2 = empty cell
A3 = empty cell
A4 = 2L

The formula would return A4

Thanks for your help


This formula must be **array-entered**

=ADDRESS(MATCH(TRUE,ISTEXT(A:A),0),1,4)

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.
--ron