A Name range referring to different cells in different worksheets

  • Thread starter Thread starter Frederick Chow
  • Start date Start date
F

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.
 
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
 
Because the name Test is not a referring range, but a string.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
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
 
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
 
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.
 
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.
 
Thanks. That seems to be the case.

Maybe I must find out other workarounds.

Anyway thanks for your help.

Frederick Chow
 
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
 
Back
Top