How to name cells on more than one worksheet?

[terry]

Hi,

I'm following the Help files on this, but it's not clear as to the format of
the Refers To value when you naming cells more than one worksheet.

Separating the worksheets with a '+' doesn't seem to work, as in:

=Sheet1!$2:$20+Sheet2!$2:$20

Anyone know how to do it?

Thanks

 

[Earl Kiosterud]

Terry,

Did you really post this at 6:49 AM today? I'm surprised no one's taken it
on.

If you want your name to give you the total of those cells, define it as:
=SUM(Sheet1!$2:$20)+SUM(Sheet2!$2:$20)
Use it like: =MyName

If you want it to refer to those cells, use
=Sheet1!$2:$20,Sheet2!$2:$20
Use it like =SUM(MyName)

Earl Kiosterud
mvpearl omitthisword at verizon period net

 

[Gord Dibben]

terry

If you set the Refers To value to =!$A$1 the name will refer to
cell A1 on whichever sheet is active.

Gord Dibben Excel MVP XL2002