Hi,

I have scoured these posts but haven't quite found what I'm looking for,
hopefully someone can help....

Is it possible to write a formula that will search a range (D18:V18), find
the last cell in that range with data, then show the cell address, not the
value of the cell, in a different cell? I have been thinking that maybe I
will need an array formula, or at worst, a macro or VBA?

Any response will be greatly appreciated.

sevi

find the last cell in that range with data

What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are
there any formulas in this range that return blanks? Do you want to include
these blanks as data?

Assuming the data type is TEXT and there are no formulas in the range that
return blanks:

=IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"")


--
Biff
Microsoft Excel MVP


"sevi61" wrote in message
...
Hi,

I have scoured these posts but haven't quite found what I'm looking for,
hopefully someone can help....

Is it possible to write a formula that will search a range (D18:V18), find
the last cell in that range with data, then show the cell address, not the
value of the cell, in a different cell? I have been thinking that maybe I
will need an array formula, or at worst, a macro or VBA?

Any response will be greatly appreciated.

sevi

hi Biff,

thank you for your prompt reply. in answer to your question; the data is
numeric and there are formulas in the range but not to return blanks. the
formula that would be in the last populated cell would be =(9-SUM(XXX:XXX))
I hope this clarifies things a little

thanks again for taking the time to help with this

regards,
sevi

"T. Valko" wrote:

find the last cell in that range with data

What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH? Are
there any formulas in this range that return blanks? Do you want to include
these blanks as data?

Assuming the data type is TEXT and there are no formulas in the range that
return blanks:

=IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"")


--
Biff
Microsoft Excel MVP


"sevi61" wrote in message
...
Hi,

I have scoured these posts but haven't quite found what I'm looking for,
hopefully someone can help....

Is it possible to write a formula that will search a range (D18:V18), find
the last cell in that range with data, then show the cell address, not the
value of the cell, in a different cell? I have been thinking that maybe I
will need an array formula, or at worst, a macro or VBA?

Any response will be greatly appreciated.

sevi

Try this:

=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) )

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sevi61" wrote in message
...
hi Biff,

thank you for your prompt reply. in answer to your question; the data is
numeric and there are formulas in the range but not to return blanks. the
formula that would be in the last populated cell would be =(9-SUM(XXX:XXX))
I hope this clarifies things a little

thanks again for taking the time to help with this

regards,
sevi

"T. Valko" wrote:

find the last cell in that range with data

What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH?
Are
there any formulas in this range that return blanks? Do you want to
include
these blanks as data?

Assuming the data type is TEXT and there are no formulas in the range that
return blanks:

=IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"")


--
Biff
Microsoft Excel MVP


"sevi61" wrote in message
...
Hi,

I have scoured these posts but haven't quite found what I'm looking for,
hopefully someone can help....

Is it possible to write a formula that will search a range (D18:V18),
find
the last cell in that range with data, then show the cell address, not
the
value of the cell, in a different cell? I have been thinking that maybe
I
will need an array formula, or at worst, a macro or VBA?

Any response will be greatly appreciated.

sevi

FWIW -
This cell reference is *Text*
And can't be used in other formulas for calculation purposes, just for
display purposes.

Must be wrapped in Indirect() to use in calculations.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
Try this:

=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) )

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sevi61" wrote in message
...
hi Biff,

thank you for your prompt reply. in answer to your question; the data is
numeric and there are formulas in the range but not to return blanks. the
formula that would be in the last populated cell would be =(9-SUM(XXX:XXX))
I hope this clarifies things a little

thanks again for taking the time to help with this

regards,
sevi

"T. Valko" wrote:

find the last cell in that range with data

What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH?
Are
there any formulas in this range that return blanks? Do you want to
include
these blanks as data?

Assuming the data type is TEXT and there are no formulas in the range that
return blanks:

=IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"")


--
Biff
Microsoft Excel MVP


"sevi61" wrote in message
...
Hi,

I have scoured these posts but haven't quite found what I'm looking for,
hopefully someone can help....

Is it possible to write a formula that will search a range (D18:V18),
find
the last cell in that range with data, then show the cell address, not
the
value of the cell, in a different cell? I have been thinking that maybe
I
will need an array formula, or at worst, a macro or VBA?

Any response will be greatly appreciated.

sevi

=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) )

Try that with these values:

D18 = 29
E18 = 41
G18 = 36

Try it like this:

=ADDRESS(18,3+MATCH(99^99,D18:V18))

Since we saved a few keystrokes by eliminating the LOOKUP call we can add
some robustness <g:

=IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"")

We don't really need the $$ signs do we? Even if you want to use that
address in another formula you'd have to reference it with INDIRECT and the
$$ signs are superfluous to INDIRECT.

=IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"")

--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
Try this:

=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) )

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sevi61" wrote in message
...
hi Biff,

thank you for your prompt reply. in answer to your question; the data is
numeric and there are formulas in the range but not to return blanks. the
formula that would be in the last populated cell would be
=(9-SUM(XXX:XXX))
I hope this clarifies things a little

thanks again for taking the time to help with this

regards,
sevi

"T. Valko" wrote:

find the last cell in that range with data

What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH?
Are
there any formulas in this range that return blanks? Do you want to
include
these blanks as data?

Assuming the data type is TEXT and there are no formulas in the range
that
return blanks:

=IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"")


--
Biff
Microsoft Excel MVP


"sevi61" wrote in message
...
Hi,

I have scoured these posts but haven't quite found what I'm looking
for,
hopefully someone can help....

Is it possible to write a formula that will search a range (D18:V18),
find
the last cell in that range with data, then show the cell address, not
the
value of the cell, in a different cell? I have been thinking that maybe
I
will need an array formula, or at worst, a macro or VBA?

Any response will be greatly appreciated.

sevi

I forgot the "exact" argument:

=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18, 0))

BUT ... I like yours better:

=ADDRESS(18,3+MATCH(99^99,D18:V18))

And I usually wait for the OP's comments before adding too much
"robustness".<g

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"T. Valko" wrote in message
...
=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) )

Try that with these values:

D18 = 29
E18 = 41
G18 = 36

Try it like this:

=ADDRESS(18,3+MATCH(99^99,D18:V18))

Since we saved a few keystrokes by eliminating the LOOKUP call we can add
some robustness <g:

=IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"")

We don't really need the $$ signs do we? Even if you want to use that
address in another formula you'd have to reference it with INDIRECT and the
$$ signs are superfluous to INDIRECT.

=IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"")

--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
Try this:

=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) )

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sevi61" wrote in message
...
hi Biff,

thank you for your prompt reply. in answer to your question; the data is
numeric and there are formulas in the range but not to return blanks. the
formula that would be in the last populated cell would be
=(9-SUM(XXX:XXX))
I hope this clarifies things a little

thanks again for taking the time to help with this

regards,
sevi

"T. Valko" wrote:

find the last cell in that range with data

What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH?
Are
there any formulas in this range that return blanks? Do you want to
include
these blanks as data?

Assuming the data type is TEXT and there are no formulas in the range
that
return blanks:

=IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"")


--
Biff
Microsoft Excel MVP


"sevi61" wrote in message
...
Hi,

I have scoured these posts but haven't quite found what I'm looking
for,
hopefully someone can help....

Is it possible to write a formula that will search a range (D18:V18),
find
the last cell in that range with data, then show the cell address, not
the
value of the cell, in a different cell? I have been thinking that maybe
I
will need an array formula, or at worst, a macro or VBA?

Any response will be greatly appreciated.

sevi

thank you! that is just the ticket. I realise I might be taking advantage of
your good nature (and your considerable knowledge of Excel) but now i need to
find a formula that will take the numeric value from that cell and use it in
a summing function. i have been tooling around with INDIRECT but as I am
pretty much a novice I haven't had much luck.

any help you could provide would be greatly apprectiated.

"T. Valko" wrote:

=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) )

Try that with these values:

D18 = 29
E18 = 41
G18 = 36

Try it like this:

=ADDRESS(18,3+MATCH(99^99,D18:V18))

Since we saved a few keystrokes by eliminating the LOOKUP call we can add
some robustness <g:

=IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8)),"")

We don't really need the $$ signs do we? Even if you want to use that
address in another formula you'd have to reference it with INDIRECT and the
$$ signs are superfluous to INDIRECT.

=IF(COUNT(D18:V18),ADDRESS(18,3+MATCH(99^99,D18:V1 8),4),"")

--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
Try this:

=ADDRESS(18,3+MATCH(LOOKUP(99^99,D18:V18),D18:V18) )

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sevi61" wrote in message
...
hi Biff,

thank you for your prompt reply. in answer to your question; the data is
numeric and there are formulas in the range but not to return blanks. the
formula that would be in the last populated cell would be
=(9-SUM(XXX:XXX))
I hope this clarifies things a little

thanks again for taking the time to help with this

regards,
sevi

"T. Valko" wrote:

find the last cell in that range with data

What is the data type? Is it TEXT or NUMERIC or is it a mixture of BOTH?
Are
there any formulas in this range that return blanks? Do you want to
include
these blanks as data?

Assuming the data type is TEXT and there are no formulas in the range
that
return blanks:

=IF(COUNTA(D18:V18),ADDRESS(18,MATCH(REPT("z",255) ,D18:V18)+3,4),"")


--
Biff
Microsoft Excel MVP


"sevi61" wrote in message
...
Hi,

I have scoured these posts but haven't quite found what I'm looking
for,
hopefully someone can help....

Is it possible to write a formula that will search a range (D18:V18),
find
the last cell in that range with data, then show the cell address, not
the
value of the cell, in a different cell? I have been thinking that maybe
I
will need an array formula, or at worst, a macro or VBA?

Any response will be greatly appreciated.

sevi