[Solved] Filter one sheet to many consistently

Discuss the spreadsheet application
Locked
Luxifer
Posts: 5
Joined: Wed Apr 20, 2011 9:39 pm

[Solved] Filter one sheet to many consistently

Post by Luxifer »

I'm new here. I looked around but couldn't find a similar problem so I'll open a new thread
I get a spreadsheet every few days from someone, always in the same format. I need to manipulate and filter it.
What I would like to do is:
Paste the new sheet over top of the old one in my workbook.
Sheet 2 references sheet 1 and then does all the calculations.
Sheet 3-9 are final worksheets, each a subset of the items on sheet 2.

As an example lets say I get data with these columns:

Id, Name, Format, Price, #inStock

I manipulate this to get

Id, Name, Format, Price, #inStock, MyId, MyName, MyPrice

Now, on sheet 3 I just want
MyId, MyName, MyPrice
for all items where #inStock >0 and format =x

On sheet 4 I just want
MyId, MyName, MyPrice
for all items where #inStock >0 and format =y

etc...

In a way that just recalculates and filters every time I paste in a new dataset.
I'm partly there, but things never seem to work like I want. It's a nifty little problem if anyone here has an idea.
From the posts I've ready, someone's able to do this 8-)
Last edited by Hagar Delest on Wed Apr 27, 2011 11:43 pm, edited 1 time in total.
Reason: tagged [Solved].
LibreOffice 3.3 on Fedora 14
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Filter one sheet to many consistantly

Post by JohnSUN-Pensioner »

There are many ways to do this: macro, database, filters, datapilot or formula. Need to clarify - fields MyId, MyName, MyPrice computed automatically? Filled in by hand?
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Filter one sheet to many consistantly

Post by JohnSUN-Pensioner »

Maybe it helps to understand
http://wmstrong.ru/images/Animation/spl ... sheets.gif

Update 11'Sep 17 Unfortunately, wmstrong.ru now belongs to others. Please, try this link
Last edited by JohnSUN-Pensioner on Mon Sep 11, 2017 8:04 pm, edited 1 time in total.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Luxifer
Posts: 5
Joined: Wed Apr 20, 2011 9:39 pm

Re: Filter one sheet to many consistently

Post by Luxifer »

Sweet, very nicely done. I've never generated a database that way, only made them from scratch.
Thank you very much.
LibreOffice 3.3 on Fedora 14
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Filter one sheet to many consistently

Post by JohnSUN-Pensioner »

On the "very nicely"- is not very nice. I noticed a few mistakes in the texts.
Do I need to correct them, to bring the cartoon in the Getting Started <Education <Base <Sample Databases? I don't know...
Glad to be helpful!
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter one sheet to many consistently

Post by Villeroy »

I noticed a few mistakes in the texts.
Do I need to correct them, to bring the cartoon in the Getting Started <Education <Base <Sample Databases?
You may add a list of corrections below the video. I don't see any mistakes.
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
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Filter one sheet to many consistently

Post by JohnSUN-Pensioner »

"Mistakes" - is only slips: missing articles, "Sheet" instead "Sheets" and similar. English is not my native language :oops:
And in fact I forgot to add instructions only: When we get a new regular file, we can rewrite it over Input.ods or reconnect a database to a new table (Edit-Database)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter one sheet to many consistently

Post by Villeroy »

Another solution in this context: Instead of creating many queries with hard coded filter criteria, one may prefer one parameter query which prompts for the criteria. Just replace the 'x' 'y' 'z' criteria with a named parameter :Which_One (no quotes, no space, no special chars). This way you get another table every time you run the query.
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
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Filter one sheet to many consistently

Post by JohnSUN-Pensioner »

Of course! You know it, I know it, other masters know it... My movie is designed for Dummies beginners, which a parameter in query could frighten. Let them make the first step. Then we are explain complex ideas
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Luxifer
Posts: 5
Joined: Wed Apr 20, 2011 9:39 pm

Re: [Solved] Filter one sheet to many consistently

Post by Luxifer »

One problem I'm having with this is, for some reason my database will only format fields as text. so I'm trying to do a calculation and I can't. I have only one of my fields as Decimal, all the rest are Text[VARCHAR], and I can't change it. Any ideas? In the spreadsheet I've done all numeric columns as Number/General.
LibreOffice 3.3 on Fedora 14
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Filter one sheet to many consistently

Post by JohnSUN-Pensioner »

There are a few assumptions. Difficult to choose the right without a sample of real spreadsheet.
In my example Price and #inStock is decimal
Decimal and Varchar.JPG
Decimal and Varchar.JPG (19.94 KiB) Viewed 9633 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter one sheet to many consistently

Post by Villeroy »

If your column is a mixture of text and numbers, the database treats the column as a text field.
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
Luxifer
Posts: 5
Joined: Wed Apr 20, 2011 9:39 pm

Re: [Solved] Filter one sheet to many consistently

Post by Luxifer »

I removed all the text (the column heading was all) and selected the column and made sure it was Decimal format. Saved the sheet, went back to the database and still it's text. Do I have to redo the database or something?
LibreOffice 3.3 on Fedora 14
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter one sheet to many consistently

Post by Villeroy »

If in doubt, reload the whole application. If that does not help, you may have overlooked some text candidates.

Download and install this extension: http://user.services.openoffice.org/en/ ... p?id=11048
Close all spreadsheets, menu:Tools>Extensions...[Add], point to the downloaded zip.
Open your sheet, call Tools>AddOns>SpecialCells>Contents and find all types of cells.
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
Luxifer
Posts: 5
Joined: Wed Apr 20, 2011 9:39 pm

Re: [Solved] Filter one sheet to many consistently

Post by Luxifer »

That worked. The original file had text down at 104000 row. <sigh> Thank you.
Nice extension, by the way. I'll be using that more.
So, is there any way that I can format my headings? I'm thinking it's easier to NOT put headings in the database and just do my own formatting and link the data below it?
LibreOffice 3.3 on Fedora 14
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter one sheet to many consistently

Post by Villeroy »

Luxifer wrote:That worked. The original file had text down at 104000 row. <sigh> Thank you.
Nice extension, by the way. I'll be using that more.
So, is there any way that I can format my headings? I'm thinking it's easier to NOT put headings in the database and just do my own formatting and link the data below it?
I know, this answer comes late. But the topic is read by many guests and I linked to it on several occasions without noticing the additional question.
Keep all column labels (headings). You can rename all columns easily in the query design. In the graphical design view that is the "Alias" field. In SQL you append the alias to the column name: SELECT "Field 1" AS "Name", "Field 2" AS "Birth", "Field 3" AS "Address" ...
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
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: [Solved] Filter one sheet to many consistently

Post by gerard24 »

Video/Gif of 3rd post is no longer available. :(
LibreOffice 6.4.5 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Filter one sheet to many consistently

Post by RoryOF »

I have changed that link to point to its site; however it doesn't download for me.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Filter one sheet to many consistently

Post by JohnSUN-Pensioner »

I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Locked