user defined name for function with relative reference to tabsheet

[Wolfgang Doerner]

dear powerusers,

after a while of absence I am trying to do something which, imho, had
worked out before but does no longer.
I want - explicitly without the use of VBA - define a function via the
define name thing.
For example (please read: example!!!), I want to add the two left
values in a1 and b1 with the result in c1. And this in a complete rows
(easy) AND on different sheets (impossible!).
To make things easier, I am talking about 1 (one) workbook with 16
tabsheets.
Ideally, I would copy an "=fct_1" into all sheets wheer applicable
into column c,
and then, upon moving the cursor to c1, define via insert/name/define
as follows:
name: "fct_1"
equals to: "=a1+b1"
Whenever I do this, fro example, on a tabsheet with the name
"bloodytabsheetname",
Excel automatically extends the formula by storing, for "fct_1",
even after manual correction:

"=bloodytabsheetname!a1+bloodytabsheetname!b1"

Please do understand that I do not at all refer to the absolute
positioning of a1 and b1 as absolute adresses - I am able to clean
that one out.
It's the name of the TABSHEET I want to get rid off as I don't want to
calculate anything, on any different tabsheet (of the same workbook)
with data from the "bllodytabsheetname" sheet...
Is there hope?

 

[Anon]

Perhaps, in future, you would use Sheet1, Sheet2, etc. as your example sheet
names, like everyone else.

 

[Harlan Grove]

...
...

Ideally, I would copy an "=fct_1" into all sheets wheer applicable
into column c,
and then, upon moving the cursor to c1, define via insert/name/define
as follows:
name: "fct_1"
equals to: "=a1+b1"

...

Define the name referring to the formula (EXACTLY AS TYPED HERE)

=!A1+!B1

AFAIK, this is a completely undocumented aspect of defined names. The
exclamation point without preceding worksheet name always refers to the
worksheet from which it's dereferenced.

 

[Wolfgang Doerner]

...
..
..

Define the name referring to the formula (EXACTLY AS TYPED HERE)

=!A1+!B1

AFAIK, this is a completely undocumented aspect of defined names. The
exclamation point without preceding worksheet name always refers to the
worksheet from which it's dereferenced.

Jesus!
That IS cool!