How do I use a drop down of sheet names to change the data pulled using the FILTER function?

Question

I am creating a performance tracker for my employees, and I'd like to have a sheet named for each person's first name, with all the metrics by date. I'd then like to create a performance page that will let me use data validation to have a drop down of employee/sheet name and date to pull in just the information for that person.

In my mind, it would look something like

=FILTER(SHEETNAME(A1)!B2:D3,SHEETNAME(A1)!B2:D2 = C1)

where A1 would be the dropdown list of names of the sheets.

I'm very self taught, and my knowledge of Sheets has a LOT of gaps, so any advice would be incredibly helpful!

Answer 1

You can do this with the INDIRECT function, which takes a cell reference as a string. INDIRECT("A1") (note the quotes) is equal to A1. The benefit of this is that you can use & to concatenate multiple strings to create the final reference.

Using this, your example would look like this:

=FILTER(INDIRECT(A1&"!B2:D3"),INDIRECT(A1&"!B2:D2") = C1)