Lista desplegable

[Salvador_2703]

Hola, tengo una matriz de referencia para la lista (=Hoja2!'$A$2:$A$230) pero esa matriz cambia según una condicional (Índice + Coincidir), quiero saber si puedo añadir en la línea de referencia para la lista un formula que indica la posición de una celda que es donde termina la lista según cambia el criterio condicional para la tabla de referencia; manteniendo el origen fijo, algo así =Hoja2!'$A$2:[=DIRECCION((COINCIDIR("",A:A,0)-1),1)], trate de ingresarlo tal cual pero me marca un error al momento de darle aceptar, me podrían instruir de si es o no posible y de ser posible como puedo generar los criterios. Ocupo office 2016 y w10

Muchas gracias.

[#2]

@Salvador_2703 

= Sheet2!$A$2:INDEX(Sheet2!A:A,MATCH("Ω",Sheet2!A:A))

should work. The term following the ":" must return a range reference on 'Sheet2' to the formula.

I have attached a workbook so that you see a translation into Spanish.

[With Excel 365 it might simply be

= Sheet2!$A$2#

but that is of no use in Excel 2016].

[#3]

@Peter Bartholomew 

Hello, thank you very much for the data, I tried to reference as you indicated, however it throws me an error "You cannot use reference operators (such as unions, intersections and intervals) or matrix constants for the data validation criteria" the problem is that the range of values ​​from which it takes the data for the list has text data that varies from 5 to 213, and at the moment of generating the change in the list it sends me a lot of blank spaces in the final list. I share a test file of what I am putting together to see if it is possible you can support me.
Thank you very much for your support.

[#4]

@Salvador_2703 

I have had a look at the workbook you provided.  Some of the formulas are pretty advanced and reminded me that filtering and sorting were not straightforward operations before Office 365!

I prefer to start by using Tables for input data so that a have structured references that adjust dynamically to the data.

The formula I proposed before does not work in cases where the apparent blanks beyond the end of an array are created by formula.  I have played with a number of formulas (including some dynamic arrays used for checking purposes) in the copy of your workbook.  Probably, the best formula I can suggest is

= INDEX(Output,1) : INDEX(Output, MATCH(TRUE,Output="",0)-1)
which simplifies to
= INDEX(Output,1):INDEX(Output,N)

if you already have a count of the number of occurrences that exist.  

ps I have just noticed part of your reply that didn't display.

Validation lists will accept range operators if you hide them within a defined Name (I have used 'ValidationList').