PARA TUDOOOOOOOO

Quanto a esta última dúvida, eu analisei melhor o arquivo. Alguém me explica pq que tem a plan BD e a plan Resumo, que tem o mesmo número de linhas e as mesmas informações?
Aquilo Pq as colunas de Resumo não são aplicadas diretamente na base? Sim, sabemos que a limitação do Cont.Valores() pode prejudicar o range dinâmico sim, até aí, ok, mas, parece que não há necessidade alguma de haver aquela outra planilha.

Estou falando muita besteira?

Analisem.

Fernando, só vi agora sua postagem, em relação a sua obs, de fato não teria lógica, mas o que aconteceu aqui, é que reduzi o arquivo e deixei somente com a parte que estava tendo problema com a formula, na realidade no arquivo original, a aba BD tem 32 colunas e a aba RESUMO 21 colunas e se ver só na coluna A temos a concatenação de 3 colunas do BD, ou seja, na Resumo é puxado somente os dados das colunas que interessam ao pessoal, e outro motivo, é que os dados que vão para a aba BD estão sendo digitados por varias pessoas e cada uma em um arquivo separado, depois eles enviam a pessoa para juntar todos na aba do arquivo matriz.
Com certeza, poderia ter ajustado varias coisas, como me solicitaram ajuda, até dei algumas idéias a eles, mas preferem desta forma, uma vez que os dados a serem digitados veem de fontes externas diferentes, da PRF e Concessionária da via e são copiados dos boletins de ocorrência em pdf. Até a idéia de converterem os PDF em arq texto e criar uma rotina para importar ja dei, mas como eu disse, pelo menos por enquanto preferem da forma que está.

De qualquer forma agradeço a ajuda e as observações.

[]s

.
Pelo que percebi esta sugestão que estou postando é menos pesada que a DESLOC, pelo menos é o que percebi numa plan que vai até a coluna DZ!!
.

A1 =ESQUERDA(ENDEREÇO(LIN();COL();4);SE(COL()<=26;1;SE(COL()<=702;2;4)))&20000-CONTAR.VAZIO(A5:A20000) 

... arrasta até a coluna DZ, assim vc terá a coluna e a linha final de cada coluna - e poderá usar a INDIRETO!!
.
A4 (já que tenho 2 linhas de títulos)

=SE(ÉERROS(CORRESP(MENOR($AD4:INDIRETO($AD$1);LIN()-3);$AD$4:INDIRETO($AD$1);0));"";ÍNDICE(M$4:INDIRETO(M$1);CORRESP(MENOR($AD4:INDIRETO($AD$1);LIN()-3);$AD$4:INDIRETO($AD$1);0)))

.

Referência indireto() é pros fortes!
Coragem!

sei que o tópico é sobre formulas dinâmicas
particularmente eu uso intervalos super dinâmicos tanto colunas quanto linhas
para linhas eu uso uma função vba já que a preciso da ultima linha de um conjunto de colunas

Public Function ULinhaRange(ByVal Letra_Coluna_ini As String, ByVal Letra_Coluna_Fim As String, Optional Nome_Aba As String) As Long   'ultima linha com dados
'Application.Volatile
    Dim C As Long, fLC As Long, L As Long
    If Nome_Aba = "" Then Nome_Aba = ActiveSheet.Name
    fLC = 105
    For C = Cells(1, Letra_Coluna_ini).Column To Cells(1, Letra_Coluna_Fim).Column
        L = Sheets(Nome_Aba).Cells(Rows.Count, C).End(xlUp).Row
        If L > fLC Then
            fLC = L
        End If
    Next
    ULinhaRange = fLC
End Function

para colunas eu uso apenas col() nas colunas de referencia , mas nem precisaria já que a adição de colunas e exclusão é feito por uma macro

Sub ColunasN(ByVal Quantidade_de_Colunas As Long, Optional Nome_Aba As String)   '(ByVal Nome_SETOR As String,

    If Nome_Aba = "" Or Nome_Aba = "PLanAtiva" Then Nome_Aba = ActiveSheet.Name
    Dim Ma As Long, n As Long
    Ma = Quantidade_de_Colunas
    With Sheets(Nome_Aba)

        .Range(TabelaSetores).Calculate
        If Limit(1) <> "AUXIa" Then Desformata
        If Ma > 2 Then
            Cf1 = Let_Num_Col(Cf)    'Cells(1,Cf ).Column
            If Cq < Ma Then    '--------------------------------------------------------------------------( insere colunas )--------
                n = Ma - Cq
                .Range(.Cells(6, Cf1 + 1), .Cells(1, Cf1 + n)).EntireColumn.Insert
                .Range(.Cells(6, Cf1 - 1), .Cells(16, Cf1)).AutoFill Destination:=.Range(.Cells(6, Cf1 - 1), .Cells(16, Cf1 + n)), Type:=xlFillDefault  ' Espande Formulas
                Cf = Letra_Col(Cf1 + n): Fc = Letra_Col(Cf1 + n + 1)
            End If
            If Cq > Ma Then  ' ------------------------------------------------------------------------( deleta colunas )--------
                n = Cq - Ma - 1
                .Range(.Cells(6, Cf1 - n), .Cells(1, Cf1)).EntireColumn.Delete
                Cf = Letra_Col(Cf1 - (n + 1)): Fc = Letra_Col(Cf1 - n)
            End If
            Cq = Ma
        End If
        .Range(TabelaSetores).Calculate
        If Limit(1) <> "AUXIa" Then Reformata
    End With

End Sub

e com isso mantenho uma tabela com as características da planilha e a partir dessa tabela posso fazer praticamente qualquer tipo de formula dinâmica
tipo essa

=SEERRO(ÍNDICE(INDIRETO(ÍNDICE($A$15:$J$15;1;CORRESP(AQ3;$A$12:$J$12;0))&105&":"&ÍNDICE($A$15:$J$15;1;CORRESP(AQ3;$A$12:$J$12;0))&$F$10);CORRESP(AR$3;INDIRETO(C15&105&":"&C15&$F$10);0);1);"N")

para falar a verdade essa tabela funciona como controle de areas nomeadas

Oi Ed, tudo bom? Qto tempo hein !
Olha, eu particularmente acho seu método umas 3.549 vezes mais complicado que o meu proposto neste tópico !

Não vou testar pq pra mim está confuso ! Mas se funciona pra vc, que bom.
Já tiro meu chapéu pra vc que criou e pra qquer pessoa que venha entender e usar isso!

Abs,

FF

e aí fernando
eu sou enrolado mesmo kkk
mas funciona sim tanto que a planilha pode pular de 1000 linha x 50 colunas até 5000 linha x 1000 colunas ou mais e ainda tenho controle dela
ela funciona quase igual a um banco de dados com tabelas , só que quando eu fiz nem sabia que existia banco de dados e chamei as tabelas de setores kkk

sobre a função que indica a ultima linha de um conjunto de colunas

=ULinhaRange("AB";"AH";"Plan3")
vai indica a ultima linha da coluna AB até AH da plan3
só que a macro esta programada para não ultrapassar de 105 para cima "fLC = 105" pq tem dados de controle

sobre a tabela de controle é algo que fiz no inicio tbm quando estava aprendendo vc já deve ter visto ela por aqui em minhas primeiras postagens
com isso eu posso trocar nome, tamanho, tipo ou até trocar de possição
nela tem posição do setor na aba, cores, formatações condicionais e de onde veio o setor

a partir dela eu crio arrays de buscas , comparações e modificações
basicamente falando é um BD dinâmico dentro da planilha

tenho ideia de modificação e passar essa tabela e macros para uma dll , mas ando desanimado com tudo e larguei de lado tem um tempo

.
Como achei o vídeo MUITO BOM, vai uma contribuição para o tópico!!
.
https://www.youtube.com/watch?v=zMp6eXVMNEo
.
PS: alguém pode dizer que é spam, mas todo bom curso tem seu preço, e, já que o vídeo é grátis, o cara aproveitou para vender o peixe dele!! ... Nada contra!!
.

.
Fernando, por ser muito bom seu vídeo, não resisti em postar aqui k ............. https://www.youtube.com/watch?v=Z1A7G2t8kxQ
.