Hi,
I am working on a project where I have a list of items that I would like to filter based on statistics of those item. Once filtered I would like to utilize a drop down on another sheet that would display just the filtered items from the original list. Additionally, if the list of items is not filtered I would like the drop down to list all items.
I have attached a test file of what I am working on.
Thanks in advance for your help guys.
Sample.xlsx
Perhaps, something like this: http://blog.contextures.com/archives...-filter-macro/
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I don't believe this will work as this requires me to use info from inside cells A3:A12 on the Position 1 sheet.
Which prior to filtering I don't know what I want.
The Position 1 sheet will have hundreds of items and approximately 100 stats, where you will chose a number of stats to filter by.
The primary filter will be determining if a cell is blank or has a value.
If you filter out blanks in column E and G on the Position 1 sheet you will generate a visible list of
Item 3
Item 5
Item 6
Item 8
Item 10
On the Testing sheet in the A6 drop down I would like to have just the filtered list visible
Item 3
Item 5
Item 6
Item 8
Item 10
If possible I am looking for a solution that does not use VBA or Macros.
See if this does what you envision.
It was necessary to clean the Item #s list. There were repeating spaces. The COUNTIF in the self diminishing list won't work without that step.
I worked this out in the Testing sheet. All the stuff is there.
In Q3:Q12 I made a clean copy of the list in the Position 1 sheet of Item #s.
In R3 I entered this array-entered formula and filled down to R12.If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
Please Login or Register to view this content.
Then in Name Manager this Dynamic Named Range formula.I named it Dyn_DropDown.Formula:Please Login or Register to view this content.
Then select the cells you want drop downs in and enter that name in the Source: DV editor.
I applied it to Testing A6:A17 and tested it. It shrinks and grows as I expected.
Let us know if this does what you want.
Dave
NavyGator,
RE: your PM. Try replacing that self-diminishing DV list formula with this array formula in R3 and filled down as far as needed.Formula:Please Login or Register to view this content.
This will change the drop downs in Testing sheet by however Position 1 sheet is Autofiltered.
WB is attached.
See if this helps: http://www.utteraccess.com/wiki/inde..._%28Non-VBA%29
Flame,
That looks like what I am wanting. I am gonna see if I can get it incorporated in to my sheet tomorrow.
Thanks for the assist.
NG
You bet. Thanks for the feedback.
Please let us know how it does on live data.
Fire,
Seems to be working pretty good. I learned a bit with the array formulas. Thanks for the assist.
I haven't looked at it yet but can i use variables instead of ranges of cells to make the formula easier to expand?
NG
Certainly. I highly recommend using Name Manager for Dynamic Named Ranges and static ranges. Static ranges are much easier to edit than ranges in formulas.can i use variables instead of ranges of cells to make the formula easier to expand?
Thanks for the feedback.
Here's some help on dynamic ranges http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks