[Solved] Filter table through a dedicated input cell value?

Discuss the spreadsheet application

[Solved] Filter table through a dedicated input cell value?

Postby Honze7 » Thu Mar 11, 2021 9:10 am

Hello,

To no avail up until now, I've been trying to automatically filter values within a sheet's table by using an input cell to look up for the searched value.

The idea would be to provide a dedicated cell within the sheet so that users may input a value and have the shown table automatically refresh and filter rows and columns according to the input cell, same way it'd happen through the filter options, only by using a directly shown cell within the sheet.

Is it possible, or should I just keep using the manual filter options?

Thanks in advance for any help or insight you may provide.
Last edited by MrProgrammer on Wed Mar 17, 2021 7:15 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Openoffice 4.1.5 - Windows 8.1 OS
Honze7
 
Posts: 3
Joined: Thu Mar 11, 2021 9:01 am

Re: How to filter table through a dedicated input cell value

Postby Villeroy » Thu Mar 11, 2021 2:27 pm

Try "Advanced Filter". Demo: download/file.php?id=36555
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29695
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter table through a dedicated input cell val

Postby steverat » Wed Mar 17, 2021 7:43 pm

I am not a good programmer but i have a file that demonstrates an Advanced Filter that might be helpful that i programed. It introduceds the macro word (function) zAF for advanced filter. You only have to name the 3 cells, not the whole area. zAF("B2","h4","M5") .It called by by a sub getzAF.
Maybe it can be adapted for you use. The output does not have to have all the field names , only the ones named in the output area (is field specific). Thank You
Attachments
AFspOut.ods
Macro for advanced filter
(23.79 KiB) Downloaded 75 times
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
steverat
 
Posts: 7
Joined: Wed Mar 17, 2021 6:17 pm

Re: [Solved] Filter table through a dedicated input cell val

Postby Villeroy » Thu Mar 18, 2021 12:50 am

You don't have to be a programmer to use a spreadsheet. The advanced filter works fairly well interactively. Define list range and criteria range, enter criteria, call menu:Filter>Advanced....
Your data look like a database import. OpenOffice comes with a database component.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29695
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter table through a dedicated input cell val

Postby steverat » Tue Mar 30, 2021 4:13 pm

I am a terrible programmer but The following free file, AFsDataBase, may be helpful. You can set up any database in a sheet , Advanced Filter, with this file. All you have to do is name the FIRST cell in each area, Input (the data to be searched) , Criteria (what looking for), and Output (search results). Put these cell references on sheet4. So Sheet 1, 2 , and 3 can be used for the data (database) and results. You could modify this to put the database any where on these 3 sheets. Then you can operate it from the menu (or the buttons on sheet1). Try the example I have.

I have other free files I programmed if you want to use Base as a back end and operate everything out of the spreadsheet (Send data to base, and retrieve data from base).
Thanks very much,
SteveRat
Attachments
AFsDataBase.ods
(35.41 KiB) Downloaded 73 times
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
steverat
 
Posts: 7
Joined: Wed Mar 17, 2021 6:17 pm

Re: [Solved] Filter table through a dedicated input cell val

Postby steverat » Tue Mar 30, 2021 5:41 pm

About using your SpreadSheet as a Database.
Sorry In my last file AFsDatabase in the OutPut Area I had the Date and Time fields in the wrong order, Not that it matters So this file puts them in the same order as the INPUT area. The new file is AFsDatabase2 that corrects this is below. (Actually the field names in the Output can be in any order except for the first field is ID , so who cares).
Thanks very much,
SteveRat
Attachments
AFsDataBase2.ods
(35.41 KiB) Downloaded 61 times
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
steverat
 
Posts: 7
Joined: Wed Mar 17, 2021 6:17 pm

Re: [Solved] Filter table through a dedicated input cell val

Postby Villeroy » Tue Mar 30, 2021 6:38 pm

All this is far too complex and will never be ready. More than 10 years ago on the old OOoforum.org there was a guy who started with similar questions and one year later (dozends of topics, hundreds of postings) he was still struggling and fighting with the same database on sheets driven by macro code. This is such an ugly waste of time, even if the "client" is diligent enough to follow. Learning how to do list keeping on sheets is a matter of 10 minutes. Learning how to use the "advanced filter" is a matter of 5 minutes.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29695
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter table through a dedicated input cell val

Postby steverat » Wed Apr 14, 2021 9:01 pm

Sorry to bother you all,
On using your spreadsheet as a database with advanced filter. I am a terrible programmer . But this file may make it easier for someone to use their spreadsheet as a database. The data to be searched (input) in in sheet1, the criteria in sheet2, and the output in sheet3. People my be able to modify it for their use. You also have the option of editing it backwards from the output to the input area ( Change of telephone number , address or Amt, etc.) It is often easier to set up and use a spreadsheet as a database than to set up a database as a database.
Thanks
Steverat
Attachments
dDataBaseAF5.ods
File someone can modify to use as a database
(50.82 KiB) Downloaded 56 times
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
steverat
 
Posts: 7
Joined: Wed Mar 17, 2021 6:17 pm

Re: [Solved] Filter table through a dedicated input cell val

Postby Villeroy » Wed Apr 14, 2021 10:31 pm

No macros, easy and foolproof to use: https://www.mediafire.com/file/pwbvasav ... r.odb/file
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29695
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter table through a dedicated input cell val

Postby steverat » Wed May 12, 2021 12:57 am

Sorry to bother you again,
On the topic of using your spreadsheet as a database. I am not a very good programmer. But I have modified my advanced filter file to be better. Remember You can use your own field names. Just type over mine out to the right. Remember to have the some of the same field names in the criteria and output (search results ) areas as the InPut area( the data).
Substitute your own data for mine if need be. And just click on the SearchDataAF menu then Search.
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
steverat
 
Posts: 7
Joined: Wed Mar 17, 2021 6:17 pm

Re: [Solved] Filter table through a dedicated input cell val

Postby steverat » Wed May 12, 2021 2:35 am

Sorry to bother you again,
I am not the best programmer, I forgot to tell you this about my previous file dDataBaseAF5.ods
I forgot to tell you all that you can add as many field names as you want from a8, b8 on out , ten even 20 to the right and add extra fields like telephone, Town, anything and it will automatically be picked up. In fact it picks up field names automatically in all 3 areas , InPut, Criteria (Sheet2), and OutPut (in Sheet3). And you can just type over my Field name to replace them with yours if you want. And replace my data with your data.
Thanks again
SteveRat
Last edited by steverat on Sat May 15, 2021 2:45 pm, edited 1 time in total.
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
steverat
 
Posts: 7
Joined: Wed Mar 17, 2021 6:17 pm

Re: [Solved] Filter table through a dedicated input cell val

Postby steverat » Wed May 12, 2021 1:58 pm

Sorry to bother you,
I sadly found out that my file AFDataBase7 occasionally fails. So I deleted it. I ran it again and again and it will sometime miss the data in the output area under a field name. Sorry The only thing that will work all the time is all the field names in the output area. If you go to sheet4 , to cell c6 and put an a in it , it will work (but only for All field names in output area).
Sorry AFDataBase7 occasionally fails. You can try dDatBaseAF5.ods above
SteveRat
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
steverat
 
Posts: 7
Joined: Wed Mar 17, 2021 6:17 pm


Return to Calc

Who is online

Users browsing this forum: klocksbass and 21 guests