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:

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:


1 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.

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .