Help with Dates
Hi I,v got stuck with this I am trying to get this to put a number that is generated in sheet1 onto sheet2 that works OK, the thing I can't get to work is to put the number into each month as it changes is this possible. all so can I make the month invisible in the out put.
This is the formula I have used
=TEXT(TODAY(),"MMMM")&'[Daily Garden Bird List.xlsx]Sheet1'!$B$30
Output = January9 (9 being the out put of sheet1)
The formula in sheet1 is =SUM(B3:B29)
I have attached a image of the screen.
| Attachment | Size |
|---|---|
| bird-tracking.xls | 29 KB |
another approach: use of DB functions
You can set up your sheets in this way:
DailyBird sheet
Use column A for the date birds spotted
Use Column B for the calc of the month (=MONTH($A1)
Use Columns C,D etc for the birds
A B C D E
Date Month Bird1 Bird2 Bird3
1/1/2010 1 1 2 4
1/2/2010 1 8 16 32
2/2/2010 2 64 128 256
MonthlyBirdList sheet
Now we're going to use the database functions of excel: in this case the database sum function DSUM
Parameter1: the database
Parameter2: the field you want to aggregate (a single species)
Parameter3: the search criteria (the number of the month)
A B C
JAN FEB
Month Month
1 2
Bird1 9 64
Bird2 18 128
Bird3 36 256
example of cell B4 =DSUM(DailyBird!$A$1:$E$4,$A4,B$2:B$3)
summarising data
hi.
If you want to just display 9 in the cell, change:
=TEXT(TODAY(),"MMMM")&'[Daily Garden Bird List.xlsx]Sheet1'!$B$30
to
='[Daily Garden Bird List.xlsx]Sheet1'!$B$30
By far the best way to analyse this information in my view is to list each siting on a single line, and to create a pivot table to summarise the data.
bird-tracking.xls
Learn more about Pivot tables here