Re: [libreoffice-users] Question on wildcards

2017-02-06 Thread Rob Jasper 
OK-

I created 1 tab with all years concatenated, and replaced the 'year-sheets' to 
select from the main concatenated list.

Thanks to all for the reactions.

Rob.

On 6 feb. 2017, at 13:48, Bruce Hohl wrote:

> For more compactness, maybe you could create a maxifs() pointed to each
> sheet then a maxifs() against those maxifs() results.  I agree with Brian
> on the point of keeping all data in one sheet if practical.  With that
> approach it's still possible to have separate tabs for each year for the
> analysis and calculation.
> 
> On Sun, Feb 5, 2017 at 7:03 PM, Brian Barker 
> wrote:
> 
>> At 22:46 05/02/2017 +0100, Rob Jasper wrote:
>> 
>>> I am looking for a way to use MAXIFS over the equivalent ranges in
>>> multiple tabs. Something like:
>>> =COUNTIF('2*'.E$2:E$200;C2)
>>> Explanation: For all ranges in tabs with name 2.* (2002, 2003, 2004,
>>> etc.), range E2:E200 give me the number of occurrences of the value define
>>> in C2. Is this at all possible?
>>> 
>> 
>> A simple workaround is to assemble copies of the relevant ranges from
>> multiple sheets on a single summary sheet. (After all, the value you are
>> trying to derive definitely relates to the data generally, not to any
>> specific year.) Then the function reference is simple. Instead of simply
>> copying values, you will want to create formulae in the summary sheet to
>> harvest values from the individual sheets. That way, the values on the
>> summary sheet will automatically update as you work on the individual
>> sheets.
>> 
>> Or you could use one sheet instead of multiple, yearly sheets in the first
>> place. After all, 1 January 2003 comes as soon after 31 December 2002 as 31
>> December 2002 did after 30 December 2002. Contrary to the theories of some
>> partygoers, nothing particularly discontinuous happens to time at the new
>> year!
>> 
>> I trust this helps.
>> 
>> Brian Barker
>> 
>> 
>> 
>> --
>> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
>> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
>> ubscribe/
>> 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
>> 
>> 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@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


-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Question on wildcards

2017-02-06 Thread Bruce Hohl 
For more compactness, maybe you could create a maxifs() pointed to each
sheet then a maxifs() against those maxifs() results.  I agree with Brian
on the point of keeping all data in one sheet if practical.  With that
approach it's still possible to have separate tabs for each year for the
analysis and calculation.

On Sun, Feb 5, 2017 at 7:03 PM, Brian Barker 
wrote:

> At 22:46 05/02/2017 +0100, Rob Jasper wrote:
>
>> I am looking for a way to use MAXIFS over the equivalent ranges in
>> multiple tabs. Something like:
>> =COUNTIF('2*'.E$2:E$200;C2)
>> Explanation: For all ranges in tabs with name 2.* (2002, 2003, 2004,
>> etc.), range E2:E200 give me the number of occurrences of the value define
>> in C2. Is this at all possible?
>>
>
> A simple workaround is to assemble copies of the relevant ranges from
> multiple sheets on a single summary sheet. (After all, the value you are
> trying to derive definitely relates to the data generally, not to any
> specific year.) Then the function reference is simple. Instead of simply
> copying values, you will want to create formulae in the summary sheet to
> harvest values from the individual sheets. That way, the values on the
> summary sheet will automatically update as you work on the individual
> sheets.
>
> Or you could use one sheet instead of multiple, yearly sheets in the first
> place. After all, 1 January 2003 comes as soon after 31 December 2002 as 31
> December 2002 did after 30 December 2002. Contrary to the theories of some
> partygoers, nothing particularly discontinuous happens to time at the new
> year!
>
> I trust this helps.
>
> Brian Barker
>
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
> ubscribe/
> 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
>
>

-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Question on wildcards

2017-02-05 Thread Brian Barker 

At 22:46 05/02/2017 +0100, Rob Jasper wrote:
I am looking for a way to use MAXIFS over the equivalent ranges in 
multiple tabs. Something like:

=COUNTIF('2*'.E$2:E$200;C2)
Explanation: For all ranges in tabs with name 2.* (2002, 2003, 2004, 
etc.), range E2:E200 give me the number of occurrences of the value 
define in C2. Is this at all possible?


A simple workaround is to assemble copies of the relevant ranges from 
multiple sheets on a single summary sheet. (After all, the value you 
are trying to derive definitely relates to the data generally, not to 
any specific year.) Then the function reference is simple. Instead of 
simply copying values, you will want to create formulae in the 
summary sheet to harvest values from the individual sheets. That way, 
the values on the summary sheet will automatically update as you work 
on the individual sheets.


Or you could use one sheet instead of multiple, yearly sheets in the 
first place. After all, 1 January 2003 comes as soon after 31 
December 2002 as 31 December 2002 did after 30 December 2002. 
Contrary to the theories of some partygoers, nothing particularly 
discontinuous happens to time at the new year!


I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Question on wildcards

2017-02-05 Thread Rob 

Hi,

I am looking for a way to use MAXIFS over the equivalent ranges in multiple 
tabs. 
Something like:

=COUNTIF(‘2*'.E$2:E$200;C2)

Explanation: For all ranges in tabs with name 2.* (2002, 2003, 2004, etc.), 
range E2:E200 give me the number of occurrences of the value define in C2

Is this at all possible?


-- 
To unsubscribe e-mail to: users+unsubscr...@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