drag cells in calc and change contents

users
#1

Hi,

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?

Cheers

#2

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

#3

Yes Brian that does help, fixes the problem and opens up several more areas of investigation

Thanks