Use INDIRECT ()
Supondo que o nome da planilha esteja na célula A1, isso deve ser feito:
=indirect("'"&A1&"'!"&$B$8")
INDIRECT cria uma referência de célula a partir de texto e fórmulas. Os e comercial concatenam as peças juntas.
Nota: o OP indica em um comentário que esta era a solução usada: "Você respondeu totalmente a minha pergunta com a = função indireta".