Re: [libreoffice-users] drag cells in calc and change contents

At 16:04 11/01/2017 +1100, Tim Lloyd wrote:
> I am a bit of a weather nerd and I store various 
> daily temperature information. (while currently 
> cowering in our one aircon room as Sydney hits 
> 41°C). I store the min and max temperatures for 
> each day. I have 12 worksheets named January 
> through December. I would like to add a separate 
> sheet where I show the number of days in each 
> month where temperatures pass a certain 
> threshold (eg. over 30 or under 10). . On sheet 13 I add:
>
> countifs(January.D4:January.D34,">30.0")
>
> To get the number of days over 30 in January. It 
> would be nice to be lazy and drag down this cell 
> for each month of the year. If I drag it 
> (understandably) changes the cell reference 
> rather than the sheet name/number (I would like 
> to see January become February, March, etc. 
> These being the names of consecutive work 
> sheets). Is there a way to achieve this either 
> using the drag function or otherwise?

On your thirteenth sheet, you will presumably 
already have a column showing the month names. 
Let's imagine they are in A1 to A12. 
Incidentally, you can create these by simply 
entering "January" (no quotes) in A1 and dragging this down column A.

Then, in B1 or wherever, enter:
=COUNTIF(INDIRECT(A1&".D4"):INDIRECT(A1&".D34");">30")
and drag this down column B.

I trust this helps.

Brian Barker


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