I am trying to create a dynamic drop down list that hides used Items for my staff.
For example: I have my data (staff names) on the cells M6:M63 (58 names). So i want to create a dynamic drop list for the cells A6:A20 & C6:C20.
On O6 cell i am trying to create a function
=FILTER(M6:M63;COUNTIF(A6:A20;M6:M63)=0) and work fine
but when I try to enclude C6:C20 as well
=FILTER(M6:M63;COUNTIF(A6:A20;C6:C20;M6:M63)=0)
i get the following message: "You've entered too many arguments for this function"
Any help please?
Hi Michalis,
Use this formula in cell O6 instead:
=UNIQUE(VSTACK(A6:A20,C6:C20,E6:E20,G6:G20,I6:I20,M6:M63),,TRUE)
Mynda
@Mynda: is VSTACK reserved to Office 365? My Office 2021 does not recognize VSTACK()
The answer is here.
https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c
Hi, @Mynda Treacy
Thank you for your answer in a such sort notice. I have to apologize because i forgot to mention that I use Excel 2021 and not 365, so when I try your suggetion i get an error.
In that case I would rearrange the data so that it is already stacked vertically because there is no easy way to stack it in a formula in earlier versions of Excel.
You should always store your data in a tabular format ( https://www.myonlinetraininghub.com/excel-tabular-data-format) because then you can easily summarise it with the built in Excel tools.
Mynda
