Estude a função indireto()...

Monte usando colunas o endereço q precisa, tipo assim
na coluna A os nomes das planilhas
na coluna B o endereço das células
na coluna C, vc usa =INDIRETO("'" & A2 & "'!" & B2)

e arrasta pra baixo

Entendi, vou colocar em anexo o modelo da planilha... teria como colocar um exemplo na aba plan1 por favor?

Coloquei essa fórmula na C9 e arrastei pro lado e pra baixo.:

=SEERRO(INDIRETO("'" & ARRUMAR($A9) &  "'!$L$" & 7+DIA(C$6));"Planilha Inválida")

Só tem um problema, alguns nomes de funcionários estão bem diferentes na planilha do que estão na coluna.
Eu coloquei Planilha Inválida para estes casos ficarem bem evidentes. Precisa arrumar o nome dessas pessoas na coluna ou na aba da planilha.

O ARRUMAR() mata os espaços à direita e à esquerda...
FF

Não consegui baixar o modelo, mas joguei na coluna que me informou e acertei o nome com o nome da aba... mas deu planilha incorreta, o que poderia ser?

Não conseguiu baixar pq ?

Deu planilha inválida na coluna toda ?

depois que fiz o download e tentei abrir a planilha, mostrou uma mensagem de planilha corrompida. Na hora que puxei a formula da erro nos nomes que estão diferentes com a aba, mas mesmo corrigindo os nome deixando idem tica com a aba da erro.

Alguem no site poderia me ajudar?

depois que fiz o download e tentei abrir a planilha, mostrou uma mensagem de planilha corrompida

Somente um problema em seu equipamento justifica o que ocorrido.
Baixei normalmente o arquivo e está ok. Das 64 linhas com nomes em Plan1, há 19 nomes que não batem com as planilhas.

mesmo corrigindo os nome deixando idem tica com a aba da erro

Poste a planilha que disse estar corrigida e com a formula aplicada, conforme o Fernando disponibilizou, para que possamos ver/entender o que foi feito e tentar auxilia-lo, caso contrario vamos continuar patinando.