CODEWITHSUNDEEP
excelnamed-rangesvba
Stupid_Intern
Stupid_Intern

Reputation: 3450

how to return values of multicell named ranges

I have some named ranges some are single cell NamedRanges others are multicelled.

When I use "=AnyNameofSingleCellNamedRange" in any cell of the workbook.It returns it's value.

But When I use "=AnyNameofMultiCellNamedRange" It returns #VALUE error saying it's of the wrong data type.

Upvotes: 1

Views: 3946

Answers (3)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

Simplest method of accessing data from a multi-celled named range is the INDEX function. For example:

=INDEX(AnyNameofMultiCellNamedRange,2)

This gives the 2nd value of a multi-celled named range.

=INDEX(AnyNameofMultiCellNamedRange,2, 3)

In a 2D array, this shows value of the cell on the 2nd row and 3rd column.

In VBA, it is even simpler - you reference it as you would any other array. For example:

x = AnyNameofMultiCellNamedRange (2,3)

Note that without the (x,y) or (x) reference in brackets after the named range, you're essentially saying "make x = to the entire array".

Upvotes: 2

Charles Williams
Charles Williams

Reputation: 23520

If you enter =MultiCellNamedRange into a cell on the same row as the range it refers to Excel does an implicit intersection and gives you the value that is the intersection of the named range and the row. if there is no intersection then you get #Value.

If you enter =MultiCellNamedRange as a multicell array formula using Control-shift-Enter then Excel does not do implicit intersection and will return an array of values from the multicell named range.

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

=AnyNameofMultiCellNamedRange is actually returning an array of values. Depending on the physical location of the formula, you may see one of the values, or the #VALUE! error.

To see the individual values, you can either enter it as an array formula over a range of the same dimensions, or you can use the Index function to see individual values. It will work as required in functions that take array arguments.

Upvotes: 1

Related Questions