http://www.tomasvasquez.com.br/blog/microsoft-office/excel-criando-listas-e-ranges-dinamicos-com-a-formula-desloc
Lendo o link, parece não ser possível resolver o problema da pergunta inicial, porém, com a função DESLOC, é possível algo muito próximo a combos dinâmicos, digo muito próximo pois não cheguei a exatamente testar no excel, somente excel online e libre office (que neste funcionou!).
Basicamente, como é possível verificar no artigo, a função DESLOC (que não cheguei a estudar a fundo), funciona da seguinte forma:
DESLOC([1]; [2]; [3]; [4]; [5]) onde os [Parâmetros] são:
1- Célula Referência;
2- Linhas a mover: positivo - sentido célula ref. para baixo, negativo - célula ref. para cima;
3- Colunas a mover: positivo - sentido célula ref. para direita, negativo - célula ref. para esquerda;
4- Linhas retorno;
5- Colunas Retorno
Então, o artigo aborda o seguinte problema, como possuir 1 combo dinâmico? partindo do princípio da fonte de dados ser um intervalo de células. Sendo este problema resolvido.
Porém queremos um segundo combo, com opções de acordo com a selecionada do primeiro combo.
Como a função DESLOC, retorna um range, ou seja, um intervalo de células é possível criar algo como:
Célula (A1) primeiro combo - lista de estados.
Em validação de dados selecione um intervalo de células com os estados (ver artigo).
Célula (A2) segundo combo - lista cidades de acordo ao selecionado no primeiro.
Adicionar no campo de intervalo a seguinte fórmula:
SE(A1=[célula do estado RS]; DESLOC([célula de início cidades RS]; 0; 0; [número de cidades (linhas) com cidades do RS]; 1); [demais conndições de outros estados])
Como comentei no início, testado no libre Office, funcionou certo,
Sendo uma possível alternativa a quem não manja muito de VBA.
exemplo.xlsx