Defined name Relative to sheets

S

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
 
S

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
 
R

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.
 
S

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
 
D

Dave Peterson

Insert|Name|Defind

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

may work for you.
 
Top