A Name range referring to different cells in different worksheets

[Frederick Chow]

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.

 

[JE McGimpsey]

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Frederick Chow]

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

 

[Arvi Laanemets]

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

 

[JE McGimpsey]

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.

 

[Frederick Chow]

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.

 

[Frederick Chow]

Thanks. That seems to be the case.

Maybe I must find out other workarounds.

Anyway thanks for your help.

Frederick Chow

 

[JE McGimpsey]

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