Forum Discussion
Lista desplegable
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.
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').