Get value of same cell from multiple sheets

Question

I'd like to get the value of cell A1 from multiple tabs in my sheet. I attempted to use INDIRECT, only to be reminded that it wouldn't work in an ARRAYFORMULA. Is there some sort of "makeshifty" formula that would do the same thing?

This is what I originally attempted:

 =ArrayFormula(IF(LEN(A2:A),INDIRECT(A2:A&"!A1"),))

Column A is a select list of the tab names in my sheet. So the first instance works, but of course, it doesn't populate down the column as I had hoped. I realize I can just copy the formula down the column, but some type of an ARRAYFORMULA would be ideal as I add rows to the list.

I found this answer, but don't see how I could apply it to my situation.

I also found this answer, but thought since it's 2.5 years old, maybe someone has discovered a clever way to avoid the drag of copying.

there is script if you interested (?) – player0 Jun 16 '20 at 21:48 — player0, Jun 16 '20 at 21:48

score 1 · Answer 1 · edited Nov 05 '21 at 04:50

Answer:

You need to do this with a script or by using the drag method - INDIRECT uses a string reference and so can't be used with an array.

More Information:

Unfortunately for the user of INDIRECT with ARRAYFORMULA, a discovery of a clever method isn't the issue - the limitation of what can be done with only formulae that is the root of this problem.

Setting up a custom function:

From the Tools > Script editor menu item, you can create scripts. An example custom formula that you could use would be as follows:

function ARRAYINDIRECT(input) {  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  return input.reduce(function(array, el) {
    if (el[0]) array.push(el[0])
    return array;
  }, []).map(x => ss.getSheetByName(x).getRange("A1").getValue());
}

Make sure to then save the script with the save icon.

In your Sheet you can then call this custom formula as so:

=ARRAYINDIRECT(A2:A)

Rundown of the function:

Takes the input range from where the formula is called - in this case A2:A
Reduces the input to remove all cells that are empty
Maps the name of the sheet which is stored in the cell to the value in A1 of the sheet it references
Returns an array to output

Get value of same cell from multiple sheets

1 Answers1

Answer:

More Information:

Setting up a custom function:

References:

Linked