Get value of same cell from multiple sheets

0
votes

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.

1
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulas
there is script if you interested (?) – player0
Have you tried the given solution? Did it work for you? If not we could try finding out why. If yes, please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos

1 Answers

1
votes

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

References: