Log In
Register
Home
New Member
May 17, 2023
Offline
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?
July 16, 2010
Offline
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
October 17, 2018
Offline
@Mynda: is VSTACK reserved to Office 365? My Office 2021 does not recognize VSTACK()
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
The answer is here.
https://support.microsoft.com/.....c10d795a9c
New Member
May 17, 2023
Offline
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.
July 16, 2010
Offline
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.myonlinetraininghu.....ata-format) because then you can easily summarise it with the built in Excel tools.
Mynda
1 Guest(s)
