How can a formula get the location of the cell it is placed in?
 

Hi-

Is there any way a formula can get the location of the cell it is placed
in? I need the row and column of the cell at recalculation time, not at the
time formula was entered, so ActiveCell won't do the job.

Thanks in advance for any help.

How can a formula get the location of the cell it is placed in?
 

Bernard,

Application.Caller will return a Range reference pointing to the
cell containing the formula.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Bernard" wrote in message
...
Hi-

Is there any way a formula can get the location of the cell it
is placed
in? I need the row and column of the cell at recalculation
time, not at the
time formula was entered, so ActiveCell won't do the job.

Thanks in advance for any help.

How can a formula get the location of the cell it is placed in?
 

Hi
=ROW() - gives you the row number of the cell this formula is in
=COLUMN() - the column number
=ADDRESS(ROW(),COLUMN()) the cell reference

--
Regards
Frank Kabel
Frankfurt, Germany

Bernard wrote:
Hi-

Is there any way a formula can get the location of the cell it is
placed in? I need the row and column of the cell at recalculation
time, not at the time formula was entered, so ActiveCell won't do the
job.

Thanks in advance for any help.

How can a formula get the location of the cell it is placed in?
 

Public MyFunction( )
set rng = Application.Caller
rw = rng.row
col = rng.column
MyFunction = rng.address
End Function

--
Regards,
Tom Ogilvy

"Bernard" wrote in message
...
Hi-

Is there any way a formula can get the location of the cell it is placed
in? I need the row and column of the cell at recalculation time, not at
the
time formula was entered, so ActiveCell won't do the job.

Thanks in advance for any help.