[Solved] Filter one sheet to many consistently
[Solved] Filter one sheet to many consistently
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
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
Last edited by Hagar Delest on Wed Apr 27, 2011 11:43 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
LibreOffice 3.3 on Fedora 14
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Filter one sheet to many consistantly
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
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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Filter one sheet to many consistantly
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
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
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
Re: Filter one sheet to many consistently
Sweet, very nicely done. I've never generated a database that way, only made them from scratch.
Thank you very much.
Thank you very much.
LibreOffice 3.3 on Fedora 14
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: [Solved] Filter one sheet to many consistently
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!
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
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
Re: [Solved] Filter one sheet to many consistently
You may add a list of corrections below the video. I don't see any mistakes.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?
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: [Solved] Filter one sheet to many consistently
"Mistakes" - is only slips: missing articles, "Sheet" instead "Sheets" and similar. English is not my native language
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)
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
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
Re: [Solved] Filter one sheet to many consistently
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: [Solved] Filter one sheet to many consistently
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
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
Re: [Solved] Filter one sheet to many consistently
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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: [Solved] Filter one sheet to many consistently
There are a few assumptions. Difficult to choose the right without a sample of real spreadsheet.
In my example Price and #inStock is decimal
In my example Price and #inStock is decimal
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
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
Re: [Solved] Filter one sheet to many consistently
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Filter one sheet to many consistently
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
Re: [Solved] Filter one sheet to many consistently
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Filter one sheet to many consistently
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?
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
Re: [Solved] Filter one sheet to many consistently
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.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?
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Filter one sheet to many consistently
Video/Gif of 3rd post is no longer available.
LibreOffice 6.4.5 on Windows 10
Re: [Solved] Filter one sheet to many consistently
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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: [Solved] Filter one sheet to many consistently
Is that better? (split a table into multiple worksheets.gif)
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
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