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 <[email protected]> > 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: [email protected] >> 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: [email protected] > 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: [email protected] 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
