How to refer to current sheet in a formula

[Poxypig]

I want to have a named formula which always refers to a cell on the
sheet it is on. When I define the formula it is automatically changed
to refer to the sheet active when I defined it. E.g. =$B$2*3 becomes
=sheet1!$B$2*3. This is no good for use on sheet 2 where I want the
formula to refer to that sheet's $B$2 (equivalent of sheet2!$B$2). I
have tried entering =!$B$2*3 which works initially but recalculates
using the $B$2 on the sheet active when the recalculation is done.

 

[Héctor Miguel]

hi, Poxypig !

-briefly-... when defining your 'named-formula'... try with: =!$b$2*3+0*now()

hth,
hector.

 

[Herbert Seidenberg]

The formula in Define Name > Refers To uses Workbook-level names.
If there are duplicate names, the Workbook-level name will be the one
on the Sheet where it was last defined.
Close and open the Define Name window for each Sheet to see
what path Excel appended to your entries.
To differentiate between Workbook-level names and Sheet-level names
use a utility like Name Lister in Walkenbach's Power Utility Pak or use
name tools mentioned elsewhere.