0

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)))
1
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.