I had to re-post this question as I worded it wrongly the first time.
here is it, is there any formula that can do the job as in this picture below? Many thanks
iPad Air, iOS 8
Hi. See reply in the original thread. "Cloned" tables, each with a filter you set up first on the Mac may be the most efficient way to do this.
SG
hi, I made a light version of my table to keep things simple. However, I my actual table is far more bigger and with around 45 different classifications the filter and cloning approach is a little impractical. Hence, I am really in desperate need of a formula to do this. Thanks again
dfg18 wrote:
my actual table is far more bigger and with around 45 different classifications the filter and cloning approach is a little impractical.
Even more of a reason to use filters instead of index columns in your original data table and formulas to extract the data. You can easily construct very sophisticated filters in the Sort & Filter panel at the right:
Cloning will work the same with larger tables, so that shouldn't be an obstacle unless you have thousands and thousands of rows.
SG
I Like this solution, however like you mentioned it yourself its cumbersome with a large set of data. Moreover, it's wouldnt work with the design which is to use only one table where the cells in the first row are used as a search bar, and the rows below it will be used to display the results.
eg, if you enter Audi under the name field the rows below should display the details of all the Audi cars, and if you want to change it to Mercedes you just change the search field to Mercedes ... in programming, the filters would be like using constants and the search field (formula) approach would be like using variables which is far more flexible and there wouldn't be the need to construct all those tables where you can accomplish the jib on a single table. Unfortunately I'm a total newbie to spreadsheets.
Hi dfg18,
There are ways to get filters to do exactly what you want. You need to have access to Numbers on a Mac. Do you have that?
If you are only interested in one value at a time then this can be done with index columns. Do you only need to search for Date but not while also searching for Name?
quinn
Hi dfg18,
Here is solution that leverages filters in an interesting way that was first suggested in these forums by Badunit. It requires a header row for your search terms and one additional column to hold a filter index (this can be hidden). Everything happens in your CarsTable.
D3 =IF(AND(OR(A3=A$2,A$2=""),OR($B$2="",$B$2=B3),OR($C$2="",$C$2=C3)),TRUE,FALSE)
The filter only shows rows that are true. I used a popup for Name and Color.
As you can see it is a robust solution. If you want Black Audis from 2013 it will show only those.
quinn
Thank you so much t quinn or answering & understanding my question totally, very much appreciated! Thanks to every one, who took he time to have ago at this question I appreciate it.
Hi dfg18,
Glad that sorted it for you. Thanks for the green tick.
The formula in column D essentially recreates the filter capabilities native to the Mac. These can be accessed in the little triangles in the column labels. SG has been promoting these for a while now. I wonder how long before they get ported to iOS?
quinn
Help with numbers(iPad) formula please
