A Name range referring to different cells in different worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frederick Chow - 05 Mar 2006 07:06 GMT

Hi all,

I have a problem of using named ranges.

Replicate the problem.

Create a name, say. "Test", which refers to
"=Sheet1!$A$1,Sheet2!$B$2,Sheet3!$C$3".

Then in Sheet1!A1, type 1, in Sheet2!A2, type 2, in Sheet3!A3, type 3.

In a cell, type "=SUM(Test)". The cell displays "#VALUE!" error. Why is
that, and are there any solutions or workaround? Thanks a lot.

Frederick Chow
Hong Kong.

Reply to this Message

JE McGimpsey - 05 Mar 2006 07:41 GMT

You're using the union operator (,) which requires that all arguments be
on the same sheet.

You could define Test as

=Sheet1!A1 + Sheet2!B2 + Sheet3!B3

and then use =Test

> Hi all,
>
[quoted text clipped - 12 lines]
> Frederick Chow
> Hong Kong.

Reply to this Message

Frederick Chow - 05 Mar 2006 07:47 GMT

Thanks for your respoense, but what if I really want to have a named range
which refers to different cells in different sheets, so that I can use the
following VBA code:

Range("CellsInDifferentSheets") = AValue

To assign a value to each of the cells in CellsInDifferentSheets name?
Thanks a lot.

Frederick Chow

> You're using the union operator (,) which requires that all arguments be
> on the same sheet.
[quoted text clipped - 21 lines]
>> Frederick Chow
>> Hong Kong.

Reply to this Message

Bob Phillips - 05 Mar 2006 07:43 GMT

Because the name Test is not a referring range, but a string.

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> Hi all,
>
[quoted text clipped - 12 lines]
> Frederick Chow
> Hong Kong.

Reply to this Message

JE McGimpsey - 05 Mar 2006 07:54 GMT

Not really - if you define Test as

=Sheet1!A1, Sheet1!B2, Sheet1!C3

then

=SUM(Test)

works fine.

Rather the concatenation operator can't create a single range from
different worksheets.

> Because the name Test is not a referring range, but a string.

Reply to this Message

Frederick Chow - 05 Mar 2006 08:15 GMT

But for me it still doesn't work if all cell references are changed to pure
relative.

Moreover, the actual reference of the named range will depend on the
location of the active cell, which is not my intention.

Frederick Chow
Hong Kong.

> Not really - if you define Test as
>
[quoted text clipped - 10 lines]
>
>> Because the name Test is not a referring range, but a string.

Reply to this Message

JE McGimpsey - 05 Mar 2006 15:46 GMT

My response was simply to demonstrate that the explanation given in
Bob's post wasn't correct. You could make the references absolute.

That won't help you with multiple sheets, of course.

There are a huge number of alternatives you can use. Here's a couple:

Const nTESTVALUE As Long = 5
Dim vMyRange As Variant
Dim i As Long
vMyRange = Array("Sheet1!A1", "Sheet2!B2", "Sheet3!B3")
For i = LBound(vMyRange) To UBound(vMyRange)
Range(vMyRange(i)).Value = nTESTVALUE
Next i

Const nTESTVALUE As Long = 10
Dim colMyRange As Collection
Dim rCell As Range
Set colMyRange = New Collection
colMyRange.Add Worksheets(1).Range("A1")
colMyRange.Add Worksheets(2).Range("B2")
colMyRange.Add Worksheets(3).Range("C3")
For Each rCell In colMyRange
rCell.Value = nTESTVALUE
Next rCell

> But for me it still doesn't work if all cell references are changed to pure
> relative.

Reply to this Message

Arvi Laanemets - 05 Mar 2006 07:47 GMT

Hi

I'm afraid you can´t use non-contignous ranges defined as named ranges.

When Sheet1...Sheet3 belong to an contignous array of sheet tabs (there are
no other sheets between them), and they always are in same order, then use
formula
=SUM(Sheet1:Sheet3!A1)

Arvi Laanemets

> Hi all,
>
[quoted text clipped - 12 lines]
> Frederick Chow
> Hong Kong.

Reply to this Message

Frederick Chow - 05 Mar 2006 08:16 GMT

Thanks. That seems to be the case.

Maybe I must find out other workarounds.

Anyway thanks for your help.

Frederick Chow

> Hi
>
[quoted text clipped - 24 lines]
>> Frederick Chow
>> Hong Kong.

Reply to this Message