Named Range

[Steph]

Can a single named range include cells from multiple sheets?

 

[Bernie Deitrick]

Steph,

Yes, as long as they are the same cells on each worksheet: Sheets 1 to
Sheet 2 cell A1 is OK, Sheet1 cell A1 and sheet2 cell B2 is not OK.

HTH,
Bernie
MS Excel MVP

 

[Charles Williams]

Hi Steph,

=sum(Sheet1!a1,Sheet2!A2,sheet3!a3)

this works OK, not sure what Bernie sees as a problem.


Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[Dave Peterson]

I thought Steph was asking about a named range. (Insert|name).

And I can have a name that refers to cells from two different worksheets:

I used this in the refers to box in that dialog:
=Sheet2!$A$1,Sheet1!$A$1

But that's not a range--but it is a name.

I guess it boils down to what Steph really meant.

Steph?

 

[Bernie Deitrick]

Dave and Charles,

You can have a named range that refers to Sheet1!A1 and Sheet2!A1, but not
Sheet1!A1 and Sheet2!B2. I'm pretty sure that is what the OP was talking
about....

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

I'd call it a Name, but not a Range.

Range's have a bunch of properties (like .parent) that this Name doesn't have.

 

[Peter T]

Wonder if the OP meant a 3D range

Refers to: =Sheet1:Sheet3!$A$1:$C$3

Not sure if a Name that defines this refers to a Range
(not according to the Immediate window), a Reference to a
group of ranges with different parents, or a Formula

Regards,
Peter

 

[Steph]

Hi Guys. Thanks for all the responses, and sorry for being so vague and
probably incorrect in my use of words.

I was trying to create a "Name" using Insert/Name. In some instances I
wanted a "Name" to be a single cell on multiple sheets, and in other
instances ranges.

So what I gather is that it is possible to have a Name on multiple sheets AS
LONG as the cells or range is the same on each sheet?

 

[Dave Peterson]

I'm not sure <vbg>.

Do you mean you want to use the same range name on each sheet?

You can do this--and the cells don't have to use the same addresses.

Just include the sheetname in the Names in workbook box (in the insert|name
dialog).

Name: Sheet1!myName
refers to: =Sheet1!$C$6:$C$13

and for "my sheet with spaces"
Name: 'my sheet with spaces'!myName
refers to: ='my sheet with spaces'!a1:a99

(same name, but different addresses).

===
You can also select your range, then type the name into the Namebox (to the left
of the formula bar). Remember to include the sheet name if you want.

If you use names in your workbooks download Jan Karel Pieterse's (with Charles
Williams and Matthew Henson) Name Manager.

You can find it at:
NameManager.Zip from http://www.bmsltd.ie/mvp

It'll make working with names much, much easier.