Using Cell Value as a Worksheet Name in Formula

Question

I wanted to use a cell's content as a sheet name, i.e.

A1	A2	A3	A4
South	North	East	West

and use the cell values (South, North, East, West) to reference cells from sheets called South, North, East, West like this:

=South!A1  
=North!A1  
etc. . . 

I wanted to use this to count all instances of TRUE in column A. My current implementation was inspired by a solution for Microsoft Excel and uses the INDIRECT() function, but it doesn't seem to work for Google Sheets:

=ARRAYFORMULA(SUM(IF(INDIRECT(A1)!$A:$A = TRUE, 1, 0)))

Answer 1

The indirect() function expects a text string that resolves into a range reference, so you will need to use string concatenation, like this:

=arrayformula( sum( indirect(A1 & "!A:A") + 0 ) )

The if() is a bit superfluous here and can be omitted provided that you coerce Boolean values true/false to integers 1/0 as shown.