Defined name Relative to sheets

[Sige]

Hi there,


I would like to create a Defined NAME: eg. a1:a10 =sige which is
relative to the sheet.
So when having the formula sum(sige) on sheet1 it will give me the
result of Sheet1!a1:a10
& on sheet2 it will return me the result of sheet2!a1:a10

Creating the name as: !a1:a10 (without the sheet name) is not a
solution is it?

Thanks for your insights, Sige

 

[JE McGimpsey]

Did you try it?

=!$A$1:$A$10

works for me.

 

[Sige]

Hi JE,

Yes I did as I thought it would be the way to go ...and it works as you
said.

But it does not recalculate!!!
(the Calculation is on Automatic) But even on F9, Shift F9, Ctrl
ShiftF9 it does not recalc.
Only when I set my cursor in the formula and Enter.

I doubt if it has anything to do with XL97 ...

Hmmmmmm... what's the catch this time again?
Sige

 

[Ragdyer]

You must type in the full name of the sheet for the named range to be WS
specific.

Sheet1!sige
Sheet2!sige
...etc.

However, if you plan ahead, you can create this named range in your first
sheet, and then any sheet *copied* from this first sheet will, thereafter,
contain this sheet specific named range.

 

[Sige]

Hi Ragdyer,

Thanks too!
Though the sheets (a little 50...) do exist already.

JE's solution is the way to go I am sure ... but it does NOT recalc for
some mysterious reason!
This behaviour occurs in all my wbks, even brand new ones!

Sigh sigh

 

[Dave Peterson]

Insert|Name|Defind

Names in Workbook: Sige
refers to: =indirect("a1:a10")

may work for you.

 

[Ragdyer]

I like that Dave!

Learn something new every day.<g>

 

[Sige]

Hi Dave,

This one does it even for me

Thanks a lot
Sige