É um erro simples. Você esqueceu a referência da planilha.
Alterar isso
ISNUMBER(B$40:B$103)
para isso
ISNUMBER('Initial data collection sheet'!B$40:B$103)
Eu tenho uma pasta de trabalho (anexada) onde um usuário entra na área de diferentes culturas em uma planilha ( Initial data collection sheet ) e, em teoria, uma segunda planilha gera uma lista de todas as culturas que eles têm (ou seja, não valores zero mas numéricos na coluna Area ). A fórmula que estou usando é:
{=IFERROR(INDEX('Initial data collection sheet'!A$40:A$103,SMALL(
IF('Initial data collection sheet'!B$40:B$103<>"",
IF('Initial data collection sheet'!B$40:B$103<>0,
IF(ISNUMBER(B$40:B$103),
ROW('Initial data collection sheet'!A$40:A$103)-ROW('Initial data collection sheet'!A$40)+1))),
ROWS('Initial data collection sheet'!A$40:'Initial data collection sheet'!A40))),
"NONE")}
em que Initial data collection sheet column A é uma lista de possíveis culturas e Initial data collection sheet column B é a coluna de áreas.
A fórmula funcionou bem sem a função ISNUMBER() incluída, mas como há dois títulos de texto no meio do intervalo, isso significava que eu estava recebendo os títulos listados, bem como as culturas presentes, o que não é bom. Com ISNUMBER() incluído, ele acredita que não há culturas presentes, independentemente de quais números estão na coluna da área.
Usando a ferramenta de avaliação de fórmulas, descobri que isso ocorre porque a função ISNUMBER() é avaliada como FALSE para todas as células do meu intervalo, números ou não. NOT(ISTEXT()) faz o mesmo que não ter nenhuma cláusula ISNUMBER() - ela também me dá os títulos, pois ela avalia NOT(ISTEXT()) as FALSE para cada célula no intervalo, independentemente do valor.
Eu tentei alterar manualmente o formato de todas as colunas B cells para numeric, bem como usar a função 'clear all' nas células e redigitar todos os valores manualmente, mas sem sucesso.
Alguém tem alguma idéia de por que o ISNUMBER() / NOT(ISTEXT()) não funciona, e como eu poderia remover os dois títulos da lista de culturas presentes?
Uma versão reduzida da pasta de trabalho pode ser vista aqui .