Re: [libreoffice-users] Re: Calc: How to refresh advanced filter automatically

....
> 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: users+help@global.libreoffice.org
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