1

When I put the following 

=INDIRECT("'2016SP'!A1:A"&"2469")

into an Excel cell, the cell only displays the value in the first cell of the range. I want the cell to have an array of values that I can reference later without having to re-fetch the data.

Can I do this with INDIRECT?

ManishChristian
  • 3,759
  • 3
  • 22
  • 50
ermSO
  • 325
  • 1
  • 2
  • 12
  • 2
    You would want to highlight the same number of rows in you destination column. with the First cell activated and all selected, put the formula in the formula bar then hit Ctrl-Shift-Enter. If done properly you will get the list you want and excel will put `{}` around the formula. – Scott Craner Jun 03 '16 at 20:51
  • A better method is to put this in the first cell `IF(row() <= 2469,2016SP!A1,"")` then copy down as far as you will ever need it go. Then you can set the 2469 to a cell value and control how much of the data is shown. Or if you want all the values of a changing dataset then a simple `=IF(2016SP!A1<>"",2016SP!A1,"")` and copy down far enough to cover the greatest number of rows. – Scott Craner Jun 03 '16 at 20:56
  • 1
    You can't store an array of values in a single cell. – gtwebb Jun 03 '16 at 21:05
  • 1
    Create a dynamic named range, and then simply reference that instead. – tigeravatar Jun 03 '16 at 22:11
  • That cell **DOES** have the array of values. You can see that by putting your cursor in the formula bar and hitting `F9`. You can return individual elements by using the `INDEX` function around the `INDIRECT`, or by pasting into a similarly sized range as an array. – Ron Rosenfeld Jun 04 '16 at 14:35

0 Answers0