Visible cells only from AutoFiltered rows as basis for list

Nomadic

New Member
Joined
Oct 31, 2005
Messages
3
Hello,

I am trying to arrive at a dropdown that only contains the visible cells from a column whose rows have been AutoFiltered.

I have tried various approaches using SUBTOTAL, but I was not able to eliminate the "0" values using a combination of an IF statement and an array formula to filter unique values.

Unfortunately, to this point I have been a complete VB noobie. I think that somehow the following post could be a part of the solution I need:
http://www.mrexcel.com/board2/viewtopic.php?t=165308&highlight=visible+rows+range

I just don't know how to use the myRange range as the basis for my dropdown (I don't care whether this is a Data Validation/List box or a control).

Or perhaps there is an even better/simpler solution?

Thank you in advance.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi Nomadic, welcome to the board!

So what would you like to do with this list? You want a list of filtered values in a drop down box somewhere, but without any 0 values? Can you give us an example of what your data is like, where your data is, how autofilter is applied and what your end result(s) should look like?
 
Upvote 0
firefytr said:
Hi Nomadic, welcome to the board! So what would you like to do with this list?

Thanks! I do not actually need to display the list in separate cells (although doing this IN ADDITION to what I really need would be very cool). I just need to use this list as the basis for a dropdown selection of some sort.

firefytr said:
You want a list of filtered values in a drop down box somewhere, but without any 0 values?

Yes. The "0" values were "junk" somehow inserted instead of the values in the hidden rows.

firefytr said:
Can you give us an example of what your data is like, where your data is, how autofilter is applied and what your end result(s) should look like?

The data is in two sheets in Excel. Sheet 1 is where I am trying to accomplish the above. The data I am filtering basically a "flat file" about 30 columns by 200 rows. The first 10 columns or so of data are AutoFilter fields. Based on the selections, the 200 rows or data are either visible or hidden.

Each row is analogous to a record in a database, so I have a "Key" column with unique values that I can use to look up any other "field" (column).

So, for instance, let's say that I apply the AutoFilter criteria, and Excel hides all of the original rows of data except for seven rows. I would like the values in the "Key" column from those seven visible rows to appear in a dropdown of some sort.

I will use these values along with VLOOKUP to pull in data from the other sheet, but I know how to do that part.
 
Upvote 0
Nomadic said:
...

So, for instance, let's say that I apply the AutoFilter criteria, and Excel hides all of the original rows of data except for seven rows. I would like the values in the "Key" column from those seven visible rows to appear in a dropdown of some sort.

I will use these values along with VLOOKUP to pull in data from the other sheet, but I know how to do that part.

Are you aware that when you change filter the contents of the data validation list will change, so will VLOOKUP results?
 
Upvote 0
Aladin Akyurek said:
Are you aware that when you change filter the contents of the data validation list will change, so will VLOOKUP results?

Changing the data validation list is what I am trying to accomplish. I discovered the VLOOKUP issue while I was Forrest Gumping my way through this, but thanks for pointting it out, because it could have really screwed me up. My workaround for that is that the 30x200 cell range is essentially duplicated on Sheet 2, and that is where I will pull in values from. So the validation list will be the "lookup_value" in the lookup statement, and "lookup_range" will be in Sheet 2.
 
Upvote 0

Forum statistics

Threads
1,214,523
Messages
6,120,039
Members
448,940
Latest member
mdusw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top