Google spreadhseet EVAL function

Question

I have a google spreadsheet with different sheets, each one representing a different week.

For example:

• 1/12 - 1/16
• 1/19 - 1/23

I want to do a chart based on the content of those sheets. Is there any way I can make a formula and extract the name of the sheet from a content of a cell?

For example something like "=EVAL(A1)!$B$4", then I would have the content from "1/12 - 1/16"!$B$4 instead of having to go through each one of the weeks of the year manually.

Thanks for the help!

Answer 1

There’s no need to use AppScript, INDIRECT is enough to read a sheet name from a cell:

=INDIRECT(A1 & "!$B$4")

However, it looks like Andy’s answer is the way to go if you want to get the sheet name from its index rather than from a cell.