how to reference the result of a filter

[stott]

I need to figure out a way to reference a filter result.

For example, say sheet1 has some data, with column labels. I have filter selected, and you can select the item you want to see from each filter.

After the user selects the appropriate filters, he/she is left with one result. Unfortunately, excel still gives the result its original Row #. Say I have 2000 rows worth of data. The column labels take up just the top row, so after filtering, the appropriate result is shown directly below the first row; however, it is still labeled with its original row #.

How can I reference the result of an arbitrary filter in another sheet of the workbook? It would be preferable to not use a macro, since this document will be disseminated to many people, most of which are laymen.

I very much appreciate any help.

Thanks!

[Palmetto]

Why not use a look up formula to obtain the value? Since filter takes criteria, you might be able to use the same criteria in a look up formula. How many values / columns are you filtering on?

[stott]

Why not use a look up formula to obtain the value? Since filter takes criteria, you might be able to use the same criteria in a look up formula. How many values / columns are you filtering on?

It's a very large data set, about 7000 rows. Essentially, the filters are used so that an end-user can narrow down to a specific row. Another tab has a report that is generated from values in said specific row. The problem is, I don't know how to write a lookup that dynamically selects the filtered result for any arbitrary search.

[Palmetto]

Could you post a sample workbook with limited data (desensitized) showing the filtered state and indicate which value you are interested in obtaining?

[stott]

Yes. So in the attached workbook, I've deleted the majority of data. The 'Vehicle Select' tab allows the end user to choose a vehicle based on filters.

The 'Report' tab generates a report based on the vehicle they've narrowed the filters down to.

There is a hidden column F, in Vehicle Select tab, that is a unique identifier.

In the Report tab, it's cell#b3 that needs to pull in the column F value for the filtered result.

Thanks!


edit: I forgot to put it in filtered state; however the filters are still selectable, and just filter it so you only have one result (ie choose just one model, one trim)

[Palmetto]

How can I reference the result of an arbitrary filter in another sheet of the workbook? It would be preferable to not use a macro, since this document will be disseminated to many people, most of which are laymen.

I don't think this is achievable with just a formula approach with your current structure.
VBA code is only way I know of to do this.

However, if you are open to a change in approach that uses dependent lists, then a formula solution is an option.

See if the attached is feasible for you. If not, you will need VBA. (Maybe someone else will chip in that can see a non-VBA method with your current structure/filter approach).

Cell B3 on the report sheet now references G9 which uses a formula to join selections into a look up string.
=B9&" "&C9&" "&D9&" "&E9

Row-9 on the Vehicle Selector sheets how uses Data Validation lists with subsequent selections filtered to narrow the choices. Named Ranges were created for the lists.

[stott]

I don't think this is achievable with just a formula approach with your current structure.
VBA code is only way I know of to do this.

However, if you are open to a change in approach that uses dependent lists, then a formula solution is an option.

See if the attached is feasible for you. If not, you will need VBA. (Maybe someone else will chip in that can see a non-VBA method with your current structure/filter approach).

Cell B3 on the report sheet now references G9 which uses a formula to join selections into a look up string.
=B9&" "&C9&" "&D9&" "&E9

Row-9 on the Vehicle Selector sheets how uses Data Validation lists with subsequent selections filtered to narrow the choices. Named Ranges were created for the lists.

The way you set it up would definitely be feasible. Do you mind explaining how you made those dropdowns instead of filters?

Normally, I would have just set it all up in a pivot table and used those filters. However; pivot table filters do not refresh based on your current view, unlike your method (and the original filters, hence my choosing them). I didn't want to have an end user select from 300 different models, trims, etc. when I could have the data 'waterfall' through the filters.

Thanks so much for your help!

[Palmetto]

Do you mind explaining how you made those dropdowns instead of filters?

The drop downs result from using Data Validation with the List option.
As I showed, several lists were created and the lists assigned to a named range. These named ranges are used as Source in the data validation.

It would be too lengthy to go into detail on the Forum, but you can learn about Data Validation using Dependent lists. Named ranges are not required, but are recommended.

Peruse this link for help on the above topics: Contextures

Post back with specific questions for more help.

[wellingtonchin]

I don't think this is achievable with just a formula approach with your current structure.
VBA code is only way I know of to do this.

However, if you are open to a change in approach that uses dependent lists, then a formula solution is an option.

See if the attached is feasible for you. If not, you will need VBA. (Maybe someone else will chip in that can see a non-VBA method with your current structure/filter approach).

Cell B3 on the report sheet now references G9 which uses a formula to join selections into a look up string.
=B9&" "&C9&" "&D9&" "&E9

Row-9 on the Vehicle Selector sheets how uses Data Validation lists with subsequent selections filtered to narrow the choices. Named Ranges were created for the lists.

How would you solve this using VBA? I am trying to do the same thing and I could incorporate the VBA into my file.

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then start your own thread in the appropriate forum.

Thanks.