Defined name Relative to sheets

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

Did you try it?

=!$A$1:$A$10

works for me.

In article <[email protected]>,
"Sige" <[email protected]> wrote:

> 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

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

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Sige" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

Insert|Name|Defind

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

may work for you.

Sige wrote:
>
> 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

--

Dave Peterson

I like that Dave!

Learn something new every day.<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Insert|Name|Defind
>
> Names in Workbook: Sige
> refers to: =indirect("a1:a10")
>
> may work for you.
>
> Sige wrote:
> >
> > 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
>
> --
>
> Dave Peterson

Hi Dave,

This one does it even for me :-)

Thanks a lot
Sige