Page 1 of 1

[Solved] Filter one sheet to many consistently

PostPosted: Wed Apr 20, 2011 9:56 pm
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-)

Re: Filter one sheet to many consistantly

PostPosted: Thu Apr 21, 2011 9:55 am
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?

Re: Filter one sheet to many consistantly

PostPosted: Sat Apr 23, 2011 8:09 pm
by JohnSUN-Pensioner
Maybe it helps to understand
http://wmstrong.ru/images/Animation/split%20a%20table%20into%20multiple%20worksheets.gif

Update 11'Sep 17 Unfortunately, wmstrong.ru now belongs to others. Please, try this link

Re: Filter one sheet to many consistently

PostPosted: Wed Apr 27, 2011 10:14 pm
by Luxifer
Sweet, very nicely done. I've never generated a database that way, only made them from scratch.
Thank you very much.

Re: [Solved] Filter one sheet to many consistently

PostPosted: Thu Apr 28, 2011 6:56 am
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!

Re: [Solved] Filter one sheet to many consistently

PostPosted: Thu Apr 28, 2011 11:54 am
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.

Re: [Solved] Filter one sheet to many consistently

PostPosted: Thu Apr 28, 2011 12:18 pm
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)

Re: [Solved] Filter one sheet to many consistently

PostPosted: Thu Apr 28, 2011 6:42 pm
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.

Re: [Solved] Filter one sheet to many consistently

PostPosted: Thu Apr 28, 2011 7:12 pm
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

Re: [Solved] Filter one sheet to many consistently

PostPosted: Wed May 04, 2011 3:14 pm
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.

Re: [Solved] Filter one sheet to many consistently

PostPosted: Wed May 04, 2011 3:47 pm
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 6604 times

Re: [Solved] Filter one sheet to many consistently

PostPosted: Wed May 04, 2011 3:58 pm
by Villeroy
If your column is a mixture of text and numbers, the database treats the column as a text field.

Re: [Solved] Filter one sheet to many consistently

PostPosted: Wed May 04, 2011 6:22 pm
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?

Re: [Solved] Filter one sheet to many consistently

PostPosted: Wed May 04, 2011 7:52 pm
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: download/file.php?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.

Re: [Solved] Filter one sheet to many consistently

PostPosted: Wed May 04, 2011 8:36 pm
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?

Re: [Solved] Filter one sheet to many consistently

PostPosted: Tue Sep 06, 2011 4:13 pm
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" ...

Re: [Solved] Filter one sheet to many consistently

PostPosted: Mon Sep 04, 2017 4:12 pm
by gerard24
Video/Gif of 3rd post is no longer available. :(

Re: [Solved] Filter one sheet to many consistently

PostPosted: Mon Sep 04, 2017 4:18 pm
by RoryOF
I have changed that link to point to its site; however it doesn't download for me.

Re: [Solved] Filter one sheet to many consistently

PostPosted: Mon Sep 11, 2017 8:07 pm
by JohnSUN-Pensioner