Existem muitas maneiras de fazer referência a um intervalo de tamanho variável. Há também várias maneiras de encontrar o último não em branco em uma coluna.
Primeiro, como encontrar a linha com o último não em branco na coluna A:A :
Vou agrupar essas fórmulas em uma instrução IFERROR que retornará 6 se houver erros. Isso ocorre porque você afirma que seus dados começam na linha 6, portanto, se algo der errado, isso permitirá que você retorne apenas A6 em vez de alguma mensagem de erro. Se você quiser um erro para mostrar, basta remover o envolvimento IFERROR .
Se os seus dados forem todos os números, use isto: IFERROR(MATCH(10^308,A:A),6)
Se seus dados forem todos de texto, use isto: IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
Se seus dados forem números e texto, encontre o valor máximo dos dois: MAX(IFERROR(MATCH(10^308,A:A),6),IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
Você também pode usar uma fórmula de matriz, mas tenho a tendência de me afastar delas, porque elas geralmente são mais lentas. Seria como mostrado abaixo e seria inserido com Ctrl + Shift + Enter . (Você saberá se as chaves {} aparecem em ambos os lados da fórmula.)
MAX(ROW(A:A)*NOT(ISBLANK(A:A)))
Em segundo lugar, como referenciar o intervalo A6:A{last non blank row} :
Como você deseja a mesma referência três vezes, pode ser uma boa ideia adicionar uma célula auxiliar. Existem várias maneiras de chegar ao que queremos, mas vou mostrar algumas. Digamos que a célula auxiliar seja B1 e a fórmula desejada esteja em B2 . Eu só vou usar a fórmula de exemplo "última linha" para texto porque acredito que é o que você é de dados. Você deve alterá-lo para o que precisar.
O método INDEX : (provavelmente a melhor opção)
B1 = IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
B2 = INDEX(A6:INDEX(A:A,B1),MODE(MATCH(A6:INDEX(A:A,B1),A6:INDEX(A:A,B1),0)))
O método INDIRECT :
B1 = "A6:A"&IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
B2 = INDEX(INDIRECT(B1),MODE(MATCH(INDIRECT(B1),INDIRECT(B1),0)))
O método OFFSET :
B1 = IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
B2 =INDEX(OFFSET(A6,0,0,B1-5),MODE(MATCH(OFFSET(A6,0,0,B1-5),OFFSET(A6,0,0,B1-5),0)))