user defined name for function with relative reference to tabsheet

  • Thread starter Thread starter Wolfgang Doerner
  • Start date Start date
W

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?
 
Perhaps, in future, you would use Sheet1, Sheet2, etc. as your example sheet
names, like everyone else.
 
...
...
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"
Click to expand...
...

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.
 
Harlan Grove said:
...
..
..

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.
Click to expand...


Jesus!
That IS cool!
 
Back
Top