Calc: How to refresh advanced filter automatically

classic Classic list List threaded Threaded
9 messages Options
relgames relgames
Reply | Threaded
Open this post in threaded view
|

Calc: How to refresh advanced filter automatically

Hi,

I have Calc file with a long list of rows, what I need is to filter those rows by some criteria entered by user.
So, if user enters "15" into some cell, list should immediately update itself and show only rows containing "15"

Any suggestions?


Regards,
Oleg.
David S. Crampton David S. Crampton
Reply | Threaded
Open this post in threaded view
|

Re: Calc: How to refresh advanced filter automatically

Oleg,

It might help us to know more about your application. There are functions  
within Calc to do this under the menu item: Data.  Using these  
database-like operations in Calc (or any other spreadsheet) requires a  
reasonably experienced level of user.  The user must place the "15" in  
exactly the correct cell and then navigate to the portion of the  
spreadsheet which will display the result.

This type of operation, IMHO, is better done in a database, ie. LO Base.  
The user input can be gathered through a Form. The results of the query  
are quick and interactive.

If you are just beginning to design this application for users please make  
a good decision about spreadsheet versus database for the "engine". I just  
finished 10 years of IT Management which included troubleshooting massive  
and tangled spreadsheet apps that should have been databases from the  
beginning.
--
David S. Crampton

On Mon, 05 Dec 2011 06:08:46 -0800, relgames <[hidden email]> wrote:

> Hi,
>
> I have Calc file with a long list of rows, what I need is to filter those
> rows by some criteria entered by user.
> So, if user enters "15" into some cell, list should immediately update
> itself and show only rows containing "15"
>
> Any suggestions?
>
>
> Regards,
> Oleg.
>
> --
> View this message in context:  
> http://nabble.documentfoundation.org/Calc-How-to-refresh-advanced-filter-automatically-tp3561522p3561522.html
> Sent from the Users mailing list archive at Nabble.com.

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
David S. Crampton
Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Calc: How to refresh advanced filter automatically

Hi :)
+1
to using a database.  I would set-up a Query and use that but normal users would probably prefer a pretty gui and the fastest way to do that is to set-up a Form based on the Query.  By default Forms will probably show a list of tables first and then Queries afterwards so it's not more difficult to set-up.  Plus you can edit a Query easily if they suddenly decide they want some extra filtering that they didn't bother to tell you about at the outset. 

Setting up a simple form is pretty easy and almost does it itself.  Joining this mailing list is tougher.  Base can even use the existing spreadsheet as its back-end table. 
Regards from
Tom :)

--- On Mon, 5/12/11, David S. Crampton <[hidden email]> wrote:

From: David S. Crampton <[hidden email]>
Subject: Re: [libreoffice-users] Calc: How to refresh advanced filter automatically
To: [hidden email]
Date: Monday, 5 December, 2011, 15:06

Oleg,

It might help us to know more about your application. There are functions within Calc to do this under the menu item: Data.  Using these database-like operations in Calc (or any other spreadsheet) requires a reasonably experienced level of user.  The user must place the "15" in exactly the correct cell and then navigate to the portion of the spreadsheet which will display the result.

This type of operation, IMHO, is better done in a database, ie. LO Base.  The user input can be gathered through a Form. The results of the query are quick and interactive.

If you are just beginning to design this application for users please make a good decision about spreadsheet versus database for the "engine". I just finished 10 years of IT Management which included troubleshooting massive and tangled spreadsheet apps that should have been databases from the beginning.
--David S. Crampton

On Mon, 05 Dec 2011 06:08:46 -0800, relgames <[hidden email]> wrote:

> Hi,
>
> I have Calc file with a long list of rows, what I need is to filter those
> rows by some criteria entered by user.
> So, if user enters "15" into some cell, list should immediately update
> itself and show only rows containing "15"
>
> Any suggestions?
>
>
> Regards,
> Oleg.
>
> --
> View this message in context: http://nabble.documentfoundation.org/Calc-How-to-refresh-advanced-filter-automatically-tp3561522p3561522.html
> Sent from the Users mailing list archive at Nabble.com.

--For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Jay Lozier Jay Lozier
Reply | Threaded
Open this post in threaded view
|

Re: Calc: How to refresh advanced filter automatically

In reply to this post by David S. Crampton
On 12/05/2011 10:06 AM, David S. Crampton wrote:

> Oleg,
>
> It might help us to know more about your application. There are
> functions within Calc to do this under the menu item: Data.  Using
> these database-like operations in Calc (or any other spreadsheet)
> requires a reasonably experienced level of user.  The user must place
> the "15" in exactly the correct cell and then navigate to the portion
> of the spreadsheet which will display the result.
>
> This type of operation, IMHO, is better done in a database, ie. LO
> Base.  The user input can be gathered through a Form. The results of
> the query are quick and interactive.
>
> If you are just beginning to design this application for users please
> make a good decision about spreadsheet versus database for the
> "engine". I just finished 10 years of IT Management which included
> troubleshooting massive and tangled spreadsheet apps that should have
> been databases from the beginning.
If possible I second this, spreadsheets are excellent at calculations
but are not very good at being databases. Please note most users are
scared of databases because they require a steeper learning curve.

Another solution, is to have a macro handle the data entry and selection.

--
Jay Lozier
[hidden email]


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Pedro Pedro
Reply | Threaded
Open this post in threaded view
|

Re: Calc: How to refresh advanced filter automatically

In reply to this post by relgames
relgames wrote
I have Calc file with a long list of rows, what I need is to filter those rows by some criteria entered by user.
So, if user enters "15" into some cell, list should immediately update itself and show only rows containing "15"
Any suggestions?
I think using a database for such a trivial task is like using a nuclear bomb in a knife fight :)

Just select the header cells, click on Data, Filter, AutoFilter

Now the user just has to select 15 from the appropriate column by clicking on the button with the down arrow.

relgames relgames
Reply | Threaded
Open this post in threaded view
|

Re: Calc: How to refresh advanced filter automatically

In reply to this post by David S. Crampton
David S. Crampton wrote
It might help us to know more about your application. There are functions  
within Calc to do this under the menu item: Data.  Using these  
database-like operations in Calc (or any other spreadsheet) requires a  
reasonably experienced level of user.  The user must place the "15" in  
exactly the correct cell and then navigate to the portion of the  
spreadsheet which will display the result.
You are right, I had to provide more details.

Spreadsheet is used to parse fixed-length messages. There are around 100 different messages, and each contains different fields. For example, message 01 contains 3 fields with length 10, and 1 field with length 5, and message 02 contains 2 fields with lengths 3 and 2.

I have XLS file (prepared by another person) like:
A | B | C | D
M02My-very-complex-message
M01 | =MID($A$1;C2;D2) | 1 | 10
M01 | =MID($A$1;C3;D3)  | 11 | 10
M01 | =MID($A$1;C4;D4)  | 21 | 10
M01 | =MID($A$1;C4;D4)  | 31 | 5

M02 | =MID($A$1;C5;D5) | 1 | 3
M02 | =MID($A$1;C6;D6) | 4 | 2

So when I need to parse a message, I enter it into A1, then go to autofilter and select message type (01, if it's message 01)
What I want is to eliminate this last step, so filter should change automatically when I enter message into A1.

Oleg.
Pedro Pedro
Reply | Threaded
Open this post in threaded view
|

Re: Calc: How to refresh advanced filter automatically

Wow! This is totally different from what you asked in your original post.

Obviously my answer doesn't apply here at all.

I think that this task would be much better solved by a small program than a spreadsheet or even a database...
relgames relgames
Reply | Threaded
Open this post in threaded view
|

Re: Calc: How to refresh advanced filter automatically

Pedro wrote
I think that this task would be much better solved by a small program than a spreadsheet or even a database...
I thought about it, but it works just fine in Calc, the only thing that disturbs me is necessity to manually change Autofilter. I tried to use macros (record and then execute), but looks like record macro functionality is broken in LibreOffice 3.4.3 - it is recorded, but it doesn't actually change the filter.
"Ing. Jiří Hladůvka" "Ing. Jiří Hladůvka"
Reply | Threaded
Open this post in threaded view
|

Re: Calc: How to refresh advanced filter automatically

In reply to this post by relgames


....

> Spreadsheet is used to parse fixed-length messages. There are around 100
> different messages, and each contains different fields. For example, message
> 01 contains 3 fields with length 10, and 1 field with length 5, and message
> 02 contains 2 fields with lengths 3 and 2.
>
> I have XLS file (prepared by another person) like:
> A | B | C | D
> M02My-very-complex-message
> M01 | =MID($A$1;C2;D2) | 1 | 10
> M01 | =MID($A$1;C3;D3)  | 11 | 10
> M01 | =MID($A$1;C4;D4)  | 21 | 10
> M01 | =MID($A$1;C4;D4)  | 31 | 5
>
> M02 | =MID($A$1;C5;D5) | 1 | 3
> M02 | =MID($A$1;C6;D6) | 4 | 2
>
> So when I need to parse a message, I enter it into A1, then go to autofilter
> and select message type (01, if it's message 01)
> What I want is to eliminate this last step, so filter should change
> automatically when I enter message into A1.
>
...

You can have a better solution
Create a PARAMETERS sheet (it can be even in an external file)
like
M011 |  1 | 10
M012 | 11 | 10
M013 | 21 | 10
M014 | 31 | 5

M021 | 1 | 3
M022 | 4 | 2

which says  :
The 1st parsing result of the message of the type M01 is found in the
source message at position 1 and is 10 chars long
etc

Let the parameters are in PAR.A1:C100 for example

In the main document you can have a hiden sheet WORK
besides the main sheet MESSAGE

where in WORK.A1 is =MESSAGE.A1
WORK.A2 is a message type =left(A1;3)


The first field:
in WORK.B1 is the offset of the 1st field
=vlookup(WORK.$A$2 &"1";PAR.$A$1:$C$100;2;0)

in WORK.C1 is the field length
=vlookup(WORK.$A$2 &"1";PAR.$A$1:$C$100;3;0)

in WORK.D1 is the 1st field text
=mid(WORK.A1;WORK.B1;WORK.C1)

The second field:
in WORK.B2 is offset of the 2st field
=vlookup(WORK.$A$2 &"2";PAR.$A$1:$C$100;2;0)

etc.


You can have prepared let's the maximal number of fields
And in the column WORK.C there will be parsed fields.
You should maintain #NA values in the column if there is less
fields


In the mai sheet MESSAGE you can refer
to the C column of the hidden WORK sheet.
As soon as you type the message you can see the result
without filtering.
Thus the parsing rules are save from editing if the PARAMETERS sheet is
read only or locked.

Regards,
Jiri














--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted