Muito boa a dica!!

Acho aformula Desloc uma das mais dificeis e magicas do Excel, gosto de usar, outra formula dificil que gostaria de saber mais Fernando e que usei uma vez mais tenho receio de usar por ser dificil de montar, pois oculpa muito tempo é BDCONTAR.

Vlw!!!!

Essa postagem do range dinâmico é uma das mais fodas... merecia um jóia e ou um sticky (y)

Muito legal, ficava alterando manualmente a referência das células.Vou utilizar muito.
Abraços.

Muito Sinistro. Util ao extremo, e me fez rever meu antigo conceito de que o procv é a funcao mais util do excel (sim, meus conhecimento sao limitados). Pra quem já tentou estudar VBA e nao chegou a lugar algum, indispensavel entender essa formular pra nao ficar perdido nos offset.
Valeu Fernando!

O arquivo está anexado ao tópico em ambos os formatos.

"Desvantagens:
1) Ainda não enxerguei desvantagem... alguém se habilita?"
.
---------------------------------- Você inutiliza todas as linhas das colunas destinadas à Desloc !!! ... Não pode nem fazer um rascunho na parte de baixo, se fizer, a Desloc vai lá incorporar o dado !!!

"Desvantagens:
1) Ainda não enxerguei desvantagem... alguém se habilita?"

COPA,

Tem um outro tópico onde o Fernando explica que usar range dinâmico com ÍNDICE() é mais rápido do que usar DESLOC(). Isso porque desloc é função volátil e índice não é.

Você inutiliza todas as linhas das colunas destinadas à Desloc !!! ... Não pode nem fazer um rascunho na parte de baixo, se fizer, a Desloc vai lá incorporar o dado !!!

Mas tem um porém, o Fernando também criou outro tópico por ai explicando que devemos evitar referenciar a linha inteira (nesse caso, usando CONT.VALORES(). Assim, vc pode usar um range dinâmico prevendo apenas 100, 1000 ou 10000 linhas, por exemplo, e pode até rascunhar acima ou abaixo delas. Sem contar que a gente fica viciado em usar CONT.VALORES(), e pegar todas as células não vazias, mas também pode-se utilizar CONT.NÚM, ou CONT.SE, por exemplo, o que permitiria, por exemplo, ter um range dinâmico com nomes de pessoas, e ainda rascunhar um cálculo na mesma coluna.

Abs

JSCopa, qto tempo...

Então, você tem razão por um lado... Mas é que do ponto de vista da aplicação geral do intervalo dinâmico, teoricamente ele pode crescer até ... .até pra sempre... então, escrever qualquer coisa, mesmo que rascunho, abaixo de um local aonde serão inseridos dados, é risco de perder o rascunho quando dados forem preenchidos.
Eu penso em banco de dados... se vc quer rascunhar qualquer coisa, vc insere linhas em branco ao final da tabela, e escreve coisas ali ?

Mas vou concordar com você num aspecto: sim, vc acaba prendendo e limitando o uso da planilha onde o intervalo dinâmico é aplicado. Mas, por outro lado, o Excel não impede de fazer rascunhos em outros locais, melhores até. Não vejo sua argumentação como desvantagem.

Como o GTSalikis disse: claro que se alguém quiser brincar diferentemente com o conceito do range dinâmico,
- Primeiro precisa dominar tudo que tem ali, e você domina pq aprendeu muito bem, lá no Orkut! rs...

- Segundo, lembrar que apesar de estarmos referenciando colunas inteiras, podemos referenciar intervalos específicos, limitando o range dinâmico em 10, 100, 1mil ou sei lá, 10mil linhas. Basta brincar com os intervalos dentro de todas as funções ou com as funções dentro da fórmula, para garantir que isso aconteça.

- E terceiro, sim, vc pode usar outras funções... para determinar onde o intevalo começa (qtas linhas abaixo da referência), pode usar um CORRESP().... Para contar os itens, pode ser Cont.Se(), ou outras funções de contagem... assim vc garante que seu range dinamico nao representa a tabela toda, mas sim um pedaço dela... garantindo assim a possibilidade de escrever abaixo da tabela sem problemas...

Enfim, existem mil maneiras....

Fernando, entendi seus argumentos !!!
.
Acho essa DESLOC show ... mas, como não trabalho com BD infinitos, me incomoda o fato dela não poder fazer mais nada na parte de baixo das colunas (afinal essa DESLOC é muito bisbilhoteira kkkkk) ... por isto prefiro as ÍNDICES/CORRESPs e PROCVs com uma boa margem de linhas para possíveis acréscimos/necessidades !!!
.

Estou com dificuldade para colocar a lista em outra ABA.
Alterei as formulas não não funciona.
Alguem pode dizer o que estou fazendo de errado?

Só uso um campo de busca: Calculo!C5
A lista esta: Cidades!A2:A1367

Deloc_linha_final =PROC('Calculo'!C5;ESQUERDA(Lista_Completa;NÚM.CARACT('Calculo'!C5));LIN(Linhas))-1
Desloc_qtd_linhas =SOMARPRODUTO(--(ESQUERDA(Lista_Completa;NÚM.CARACT('Calculo'!C5))='Calculo'!C5))
Linhas =INDIRETO("Cidades!2:"&CONT.VALORES('Cidades'!$A:$A))
lista_completa =INDIRETO("Cidade!A2:A"&CONT.VALORES('Cidades'!$A:$A))
lista_Filtrada =DESLOC('Cidades'!$A$1;Desloc_Linha_Final;0;Desloc_Qtd_Linhas*-1)
Lista_Validação =SE(OU('Calculo'!C5="";NÚM.CARACT('Calculo'!C5)>5);Lista_Completa;Lista_Filtrada)

Alterei estas formulas conforme abaixo, mas tambem não funciona.
Linhas =INDIRETO("Cidades!2:"&CONT.VALORES('Cidades'!$A:$A))
lista_completa =INDIRETO("Cidades!A2:A"&CONT.VALORES('Cidades'!$A:$A))

Estava lendo e baixei os exemplos, só não consegui ajustar para a minha questão, tambem quem mandou não estudar formulas e se dedicar a outras coisas, mas a questão é a seguinte.

Tenho uma determinada coluna com formulas que logicamente retornam os dados de outra, e uma outra em que utilizo formula para contar ou somar estes resultados, então criei um range dinâmico para utilizar dentro de uma outra formula, só que o Desloc pega até a ultima celula com formula independente se está com o resultado ou não, e eu gostaria que pegasse até a ultima celula com Resultado.

Se precisar depois coloco um modelo, mas é o mesmo que coloquei no tópico :
SOMARPRODUTO entre periodos de Horas [Resolvido]
viewtopic.php?f=20&t=18211

Já estou saindo do serviço, mais tarde vejo.

[]s

Mauro em que tabela e em que coluna ???

não use o cont.valores(), use o cont.se(intervalo;"<>") . . .

Assim (por exemplo, na coluna G):
CONT.SE(G:G;"<>")