Visible cells only from AutoFiltered rows as basis for list

[Nomadic]

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.

 

[Zack Barresse]

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?

 

[Nomadic]

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.

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.

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.

 

[Aladin Akyurek]

...

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?

 

[Nomadic]

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.