Search code examples
arraysexcelarray-formulasnamed-ranges

Excel - pass an array into a function

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.

Solution

  • Indirect might do what you want it to.

    In Sheet1 I created 3 named ranges:

    enter image description here

    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)

    enter image description here

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

    enter image description here