Can a single named range include cells from multiple sheets?

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

"Steph" wrote in message
...
Can a single named range include cells from multiple sheets?

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


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

"Steph" wrote in message
...
Can a single named range include cells from multiple sheets?

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?

Charles Williams wrote:

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


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

"Steph" wrote in message
...
Can a single named range include cells from multiple sheets?





--

Dave Peterson

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" wrote in message
...
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?

Charles Williams wrote:

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


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

"Steph" wrote in message
...
Can a single named range include cells from multiple sheets?





--

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.


Bernie Deitrick wrote:

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" wrote in message
...
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?

Charles Williams wrote:

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


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

"Steph" wrote in message
...
Can a single named range include cells from multiple sheets?





--

Dave Peterson


--

Dave Peterson

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

-----Original Message-----
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.


Bernie Deitrick wrote:

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" wrote in message
...
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?

Charles Williams wrote:

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


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

"Steph" wrote in message
...
Can a single named range include cells from
multiple sheets?





--

Dave Peterson


--

Dave Peterson

.

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?


"Peter T" wrote in message
...
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

-----Original Message-----
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.


Bernie Deitrick wrote:

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" wrote in message
...
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?

Charles Williams wrote:

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


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

"Steph" wrote in message
...
Can a single named range include cells from
multiple sheets?





--

Dave Peterson


--

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.



Steph wrote:

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?

"Peter T" wrote in message
...
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

-----Original Message-----
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.


Bernie Deitrick wrote:

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" wrote in message
...
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?

Charles Williams wrote:

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


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

"Steph" wrote in message
...
Can a single named range include cells from
multiple sheets?





--

Dave Peterson


--

Dave Peterson

.


--

Dave Peterson