So let’s say I have a table of data. One column is ‘Title’ and another is ‘Type’. Now let’s also say I wish to use ‘Data > Validity’ with a drop-down to allow a selection list of only a subset of the data in this table. For example I only want the ‘Title’ when the value in the ‘Type’ column matches a specific string (e.g. ‘Test’). I can accomplish this with Array functions but am curious as to the efficiency of any given method.
The following Array function…
{=IF(EXACT(B:B, “Test”), A:A, “”)}
…Returns the value in column A when the value in column B is ‘Test’, otherwise a empty string.
I can either put this array function directly into the field in Data > Validity (Cell Range) and it works. I could also use the array function in a separate column on the sheet and use the column reference as the input to Data > Validity. It seems that the latter method would be more efficient as LibreOffice only has to execute the array function one time but this leaves a bad taste in my mouth as I’ve now got a column of data with no header (using column references as I am I can’t find a way to allow the column header to exist and still have the array function work. So I prefer the former option as now I don’t have any duplicate data anywhere (I liken this to sort of database normalization, which I know isn’t strictly relevant here but I like the … clean-ness(?) of the solution). Having the function inside the Data Validity field also has a drawback of existing uniquely in every cell within which I want said validity, so if I have to change it I have to change a bunch.
Another option might be to go to column C, do a non-array version of the function and then just drag it down. I don’t know if this is more efficient or not but I lose the ability to have an infinitely expandable table without the need to also extend the ‘Column C’ function.
So have others done in such a situation? Any other elegant solutions out there? Any info on how performance is impacted given the different options? The data set is about 500 rows. I prefer not to use Macros for portability.