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

[Bernard]

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.

 

[Chip Pearson]

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

 

[Frank Kabel]

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

 

[Tom Ogilvy]

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