Excel - pass an array into a function

Question

On one sheet in my file I have a number of arrays defined as named ranges. One another sheet I'd like to use a drop down, or something similar to select the name of one of the named range and have the data/contents of that named range populate a range on the second sheet. Is this possible WITHOUT VBA? Is there an array formula that will do this?

One, not entirely elegant, method that I've thought of is using the index function and copy this within a range of cells equivalent in size to the size of the largest named range. Something like:

=INDEX(range_1,ROW(),COLUMN())

This requires me to be able to pass the name of a named range into a function though. Is this possible?

Answers to either or both of these questions would be greatly appreciated.

Without that, the only other way I can think to do this is using a brute force, offset look up, which I'd prefer not to do.

Thanks for your help.

Are the named ranges all of the same size?

John Coleman
– John Coleman

2016-03-17 01:36:34 +00:00
Commented Mar 17, 2016 at 1:36 — John Coleman
– John Coleman, Commented Mar 17, 2016 at 1:36
No they are all different sizes.

getting-there
– getting-there

2016-03-17 02:38:15 +00:00
Commented Mar 17, 2016 at 2:38 — getting-there
– getting-there, Commented Mar 17, 2016 at 2:38

John Coleman · Accepted Answer · 2016-03-17 01:49:27Z

2

Indirect might do what you want it to.

In Sheet1 I created 3 named ranges:

Then in Sheet2, I

1) Put these names in Column A

2) Used a data validation list linked to column A to place a name in a cell (C2)

3) Used the array formula {=INDIRECT(C2)} (accepted with Ctrl+Shift+Enter) in the cells that I wanted to hold the array (C4:E5)

When C2 is changed via the drop-down, the array is automatically changed:

answered Mar 17, 2016 at 1:49

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

getting-there Over a year ago

Can this be used for ranges or different size?

getting-there Over a year ago

Just answered my own question. That's brilliant John. I appreciate your help.

Collectives™ on Stack Overflow

Excel - pass an array into a function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related