i have 2 sheets 1 has all the data in table format and other has drop down box in b1 CELL...i managed to get all the dates from sheet data to populate in drop down box BUT what i i am trying is to get the filtered data only to display which is not working....e.g if i filter the data for type books in sheet named data then i want the that drop down display only the dates with filtered data which gets displayed on sheet data after filter n not all the data , ...m attaching the file
Administrative Note:
Is your forum profile showing the version of Excel that you need this to work for?
Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.
The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.
Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Try to formatted as date on your data validation
How is that going to help, @azumi? I don't think it addresses the filtering issue.
ms version is update in my profile...i cant seem to see where the excel version needs to be specifically updates
Here's one way:
Add column to Table on "data" tab. formula in column:
=SUBTOTAL(3,[@Date])
Put this formula somewhere else (it's in cell F1 on the attached, but you can put it anywhere) ("Filtered" is the column header I gave to the new column in the table)
=FILTER(Table1[Date],Table1[Filtered]=1)
In your Drop-down Data Validation, choose "List", and put this in Source:
=data!$F$1# (where F1 is whatever cell you put the formula above in.
Another way:
1. Create a sheet called 'hidden'.
2. In A1 of the new sheet: =LET(x,Table1[Date],FILTER(x,SUBTOTAL(3,OFFSET(x,SEQUENCE(ROWS(x))-1, 0, 1))))
3. Data validation for the drop-down: =hidden!$A$1#
Worksheet or Tab name : data
Pls try this VBA Code
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks