Given the range H5:S5, how can I have Excel return the cell address of
the first value that appears in that range.

=MATCH(1,--(H5:S50),0) entered as an array formula returns 4; this
value is in cell K5. How can I get that cell address returned?

Thanks.

Dave

Sorry, if you know the range, don't you already know the first cell
address?

Or do I not understand what you need.

Does this work?

=ADDRESS(ROW(H5),COLUMN(INDEX(H5:S5,1,1)))

or

=ADDRESS(ROW(H5),COLUMN(H5))


HTH,
JP

On Mar 19, 1:02*pm, Dave F wrote:
Given the range H5:S5, how can I have Excel return the cell address of
the first value that appears in that range.

=MATCH(1,--(H5:S50),0) entered as an array formula returns 4; this
value is in cell K5. *How can I get that cell address returned?

Thanks.

Dave

I don't think you understand my question. The first cell in that
range that has a value in it is K5. I want to know how to return that
address.

On Mar 19, 1:14*pm, JP wrote:
Sorry, if you know the range, don't you already know the first cell
address?

Or do I not understand what you need.

Does this work?

=ADDRESS(ROW(H5),COLUMN(INDEX(H5:S5,1,1)))

or

=ADDRESS(ROW(H5),COLUMN(H5))

HTH,
JP

On Mar 19, 1:02*pm, Dave F wrote:



Given the range H5:S5, how can I have Excel return the cell address of
the first value that appears in that range.

=MATCH(1,--(H5:S50),0) entered as an array formula returns 4; this
value is in cell K5. *How can I get that cell address returned?

Thanks.

Dave- Hide quoted text -

- Show quoted text -

Let me try to make this more concrete.

Given the range H5:S5, how do I return the cell address of the first
cell in that range to contain a value?

Here's an example of the data:

H5 | I5 | J5 | K5 | L5 | M5 | N5 | O5 | P5 | Q5 | R5 | S5
1 3 5 6 3

I want to get Excel to return the address K5, as that is the first
cell, from left to right, in the range H5:S5, that has data in it.

Hopefully this is more clear.

Thanks,

Dave


On Mar 19, 1:27*pm, Dave F wrote:
I don't think you understand my question. *The first cell in that
range that has a value in it is K5. *I want to know how to return that
address.

On Mar 19, 1:14*pm, JP wrote:



Sorry, if you know the range, don't you already know the first cell
address?

Or do I not understand what you need.

Does this work?

=ADDRESS(ROW(H5),COLUMN(INDEX(H5:S5,1,1)))

or

=ADDRESS(ROW(H5),COLUMN(H5))

HTH,
JP

On Mar 19, 1:02*pm, Dave F wrote:

Given the range H5:S5, how can I have Excel return the cell address of
the first value that appears in that range.

=MATCH(1,--(H5:S50),0) entered as an array formula returns 4; this
value is in cell K5. *How can I get that cell address returned?

Thanks.

Dave- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

On Mar 19, 1:43*pm, Dave F wrote:
Let me try to make this more concrete.

Given the range H5:S5, how do I return the cell address of the first
cell in that range to contain a value?

Here's an example of the data:

H5 | I5 | J5 | K5 | L5 | M5 | N5 | O5 | P5 | Q5 | R5 | S5
* * * * * * * * * *1 * * 3 * *5 * * *6 * *3

I want to get Excel to return the address K5, as that is the first
cell, from left to right, in the range H5:S5, that has data in it.

Hopefully this is more clear.

Thanks,

Dave

On Mar 19, 1:27*pm, Dave F wrote:



I don't think you understand my question. *The first cell in that
range that has a value in it is K5. *I want to know how to return that
address.

On Mar 19, 1:14*pm, JP wrote:

Sorry, if you know the range, don't you already know the first cell
address?

Or do I not understand what you need.

Does this work?

=ADDRESS(ROW(H5),COLUMN(INDEX(H5:S5,1,1)))

or

=ADDRESS(ROW(H5),COLUMN(H5))

HTH,
JP

On Mar 19, 1:02*pm, Dave F wrote:

Given the range H5:S5, how can I have Excel return the cell address of
the first value that appears in that range.

=MATCH(1,--(H5:S50),0) entered as an array formula returns 4; this
value is in cell K5. *How can I get that cell address returned?

Thanks.

Dave- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

OK, I answered this question on my own...something like
=CELL("address",OFFSET(I5,,(MATCH(1,--(I5:T50),0)-1))) entered as an
array returns the cell address.

Thanks,

Dave

On Wed, 19 Mar 2008 10:02:04 -0700 (PDT), Dave F wrote:

Given the range H5:S5, how can I have Excel return the cell address of
the first value that appears in that range.

=MATCH(1,--(H5:S50),0) entered as an array formula returns 4; this
value is in cell K5. How can I get that cell address returned?

Thanks.

Dave


=ADDRESS(ROW(H5:S5),COLUMN(H5:S5)-1+MATCH(TRUE,len(H5:S5)0,0))

entered as an **array** formula.

It could be simplified to:

=ADDRESS(ROW(H5),COLUMN(H5)-1+MATCH(TRUE,len(H5:S5)0,0))

or even:

=ADDRESS(5,7+MATCH(TRUE,len(H5:S5)0,0))


depending on your specific requirements.
--ron