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

Discuss the spreadsheet application
Post Reply
Honze7
Posts: 3
Joined: Thu Mar 11, 2021 9:01 am

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

Post by Honze7 »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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
steverat
Posts: 12
Joined: Wed Mar 17, 2021 6:17 pm

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

Post by steverat »

On using you spreadsheet as a DataBase
Below is a free file (with macros included so you do not have to program) to do it.
I am not a good programmer but i have a file that demonstrates an Advanced Filter that might be helpful that i programed.
Maybe it can be adapted for you use.

INSTRUCTIONS
This is my new file AFDataBas8. On the menu , click on SearchDataAF , then Search (Or just click on the SearchDataAF button). Go to Sheet 3. That is where your results are.

NOTE: IF YOU SCROLL DOWN CLOSE TO THE BOTTOM OF THE PAGE TO A NEW SECTION , YOU WILL FIND A NEWER VERSION AFDataBas11, which is better than version 8.

You Can Ignore These Optional Advanced Instruction immediately below!
Optional Advanced instructions
You can just ignore the extra menu items and features if you want.
Works in Windows for me. May not work in Linux.
UPDATE My best file is below AFDataBas8.ods (It is better than AFspOut.ods) AFDataBas8.ods is self expanding. As long as you start in A8 you can type over my field names and use your own. You can also add as many field names as you want out to the right B8, C8,D8,E8, F8, etc. to connected cells to the right and it will auto mark the whole area automatically. No need to mark any areas. Just click on SearchDataAF on the menu , click on search.

OPTION FOR ONLY SOME OF THE FIELDS IN THE OUTPUT
Now say you do not want all the fields in the output, just a few. This is my new file AFDataBas8. It can be used to have all the fields in the output area (the default) or just some of the fields in the output. If you just want Some of the field names in the output do the following. In Sheet 4, in cell c6 put a small s for some (fields) in the output. In Sheet 3 output starting with cell A8 to the right type the fields you want. Say you only want ID and Amt. Type ID in A8 and Amt in B8. (Delete the other field names in Sheet3.)
Then just go to the menu and click SearchDataAF then Search and it should work.
Remember you can replace my field names with any you want and it is self expanding. ( Also remember to save the file after adding new data)
I had to modify AFDataBas8.ods because there was a slight error in documentation on Sheet3 Thank You
Attachments
AFDataBas8.ods
SpreadSheet as a Database
(58.24 KiB) Downloaded 127 times
Last edited by steverat on Wed Aug 11, 2021 4:01 pm, edited 9 times in total.
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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
steverat
Posts: 12
Joined: Wed Mar 17, 2021 6:17 pm

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

Post by steverat »

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 198 times
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
steverat
Posts: 12
Joined: Wed Mar 17, 2021 6:17 pm

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

Post by steverat »

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 188 times
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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
steverat
Posts: 12
Joined: Wed Mar 17, 2021 6:17 pm

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

Post by steverat »

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 177 times
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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
steverat
Posts: 12
Joined: Wed Mar 17, 2021 6:17 pm

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

Post by steverat »

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: 12
Joined: Wed Mar 17, 2021 6:17 pm

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

Post by steverat »

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: 12
Joined: Wed Mar 17, 2021 6:17 pm

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

Post by steverat »

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: 12
Joined: Wed Mar 17, 2021 6:17 pm

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

Post by steverat »

Sorry to bother you again,
On using a spreadsheet as a database with Advanced Filter. I am not a good programmer but this might help. This is my new file AFDataBas11.

INSTRUCTIONS On the menu , click on SearchDataAF , then Search (Or just click on the SearchDataAF button). It will automatically take you to where your results are.

Seems to work in Windows for me( the person who asked at the top is using Windows)
Sorry if it does not work in Linux. Later I may reprogram it for Linux and both if i get time. Sorry

Optional Advanced instructions you can ignore.
You can just ignore the extra menu items and features if you want.
SearchDataAF can be used to have all the fields in the output area (the default) or just some of the fields in the output. If you just want Some of the field names in the output do the following. In Sheet 1, in cell M3 put a small s for some (fields) in the output. In Sheet2 output starting with cell A8 to the right type the fields you want. Say you only want ID and Amt. Type ID in A8 and Amt in B8. (Delete the other field names in Sheet2.)
Then just go to the menu and click SearchDataAF then Search and it should work.
Remember you can replace my field names with any you want and it is self expanding. ( Also remember to save the file after adding new data) .If you do not like AFDataBas11, You can try AFDatabas8 above. Sorry if it does not work in Linux. Later I may reprogram it for Linux and both if i get time. Sorry
Thank you
steverat
Attachments
AFDataBas11.ods
Using SpeadSheet as a Data Base (Advanced Filter)
(55.94 KiB) Downloaded 128 times
Last edited by steverat on Wed Aug 11, 2021 4:34 pm, edited 6 times in total.
OpenOffice 4.1.8 in Windows 10 AMD A8 chip
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

No matter how much StarBasic code you throw at the problem, this bird won't fly. Either it throws errors (variable unset) or it does nothing.
The advanced spreadsheet filter works fairly well for anybody who is willing to learn about using a it. A spreadsheet is a flexible freehand tool for educated users.

For anybody else (e.g. computer illiterate computer "users"), a database form simply works.
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
Post Reply