CODEWITHSUNDEEP
excelexcel-formulaexcel-2007
Tong Wang
Tong Wang

Reputation: 1612

Excel 2007: how do I reference a non-consecutive named range?

I have 5 cells on the first row: A1 is 1, B1 is 2, C1 is 3, D1 is 4 and E1 is 5. Now I want to pick the odd numbers and name them "list_a_odd", so I hold CONTROL and select A1, C1 and E1, and type "list_a_odd" in the name box and hit ENTER. Now in A2, if I type "=SUM(list_a_odd)", it'll give me the result of 9, which is correct and expected. But if I type in "=list_a_odd", it gives me an error "#VALUE!".

However, if I select all 5 cells (A1 through E1) and name it "list_a", and I put "=list_a" in A2, it'll show "1" in the cell, and I can drag A2 to E2 to show all the 5 numbers in list_a. So that looks like only a problem when referencing non-consecutive named ranges in a cell. Is this an excel bug?

Upvotes: 1

Views: 4695

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

The list_a behaviour you describe is an example of the implicit intersection feature of ranges. list_a refers to a continuous range A1:E1, a formula expecting a single cell, when placed in the A to E column range using this range reference works out the intersection point and returns that cell.
That is, a formula in C2 =A1:E1 or =list_a_odd actually retuns a refence to just C1.

As you say, list_a_odd is a discontinuous multi area range. And implicit intersection doesn't apply to discontinuous ranges.
Some (but not all) functions work with discontinuous ranges, including SUM, that's why =SUM(list_a_odd) works as expected, just as =SUM(A1,C1,E1) works too.

EDIT

To return a continuous range that represents a discontinuous range I would use a user defined function combined with an array formula.

In the UDF use the Range.Areas property to loop through each of the sub ranges in a discontinuous range (remember that each item in Areas can be a single cell or a range in its own right). Build up an array of values to return to the array formula on the sheet.

The exact details will depend on your specific requirements and how generalised you want to make the UDF. Have a go, and post again if you need more help

Upvotes: 4

Related Questions