Bem, primeiro, não é assim que o Excel esperaria seus dados, e é por isso que você provavelmente terá que usar alguma macro VBA para que isso seja resolvido da maneira que você descreveu.
O Excel espera que esses dados sejam organizados assim:
cabeçalho; Métrica 1; Métrica 2; Métrica 3
E seria ainda mais fácil criar um resultado como você pediu, quando os dados brutos seriam nessa ordem.
No entanto, para resolver isso como uma macro, você teria que obter uma lista de seus nomes, criar alguns diálogos para selecionar um nome, criar a macro básica que levaria o nome adicionar o cabeçalho e criar o filtro automaticamente e criar um botão ou tecla de atalho para atribuí-lo com a macro.
Outra maneira seria usar um filtro avançado: link
Há apenas algumas amostras com o comportamento de atualização, mas consegui executar isso com essa configuração:
Table1:
A | B | C
-----------------------------------------------
Header | Metric 1 | Name1 (this is your filter)
Name 1 | 100 |
Name 2 | 300 |
Name 3 | 200 |
Table2:
A
----------------
Header
="=" & Table1!C1
'=Header
Em seguida, você configura um filtro adaptado para a Tabela1! A1: B4 e com os critérios da Tabela2! A1: A3.
Essas são suas opções, eu diria.