I am attempting to build an excel spreadsheet where there are tabs at the bottom of the excel sheet for each of the 12 months - within the 'month' sheets pulls data from another sheet. It should only pull the data though if the data is within the same month.
i.e. "date received" in A column "Fees" in B column
Should I be using a pivot table? Or can I write a formula instead?
I started writing one but I don't know if I am going along the right lines at all!
=IF(ISERROR(INDEX($A$1:$B$5,SMALL(IF(AND($A$1:$A$5>=$C$1,$A$1:$A$5<=$D$1),ROW($A$1:$A$5)),ROW(1:1)),2)),"",INDEX($A$1:$B$5,SMALL(IF(AND($A$1:$A$5>=$C$1,$A$1:$A$5<=$D$1),ROW($A$1:$A$5)),ROW(1:1)),2))
Where A1:B5 is my table. (column A is the date and B are the fees). C1 and D1 is are the dates of the month (i.e. 1st October and 31st October).
What are peoples thoughts?
thank you
Yes, I think you were on the right track with the pivot table idea. It would be, by far, the simplest way to achieve what I think you want: taking a master list of all dates & fees and producing a month by month summary of the data. As a first step in C1 (and then copy down) put a formula to identify the month for the date in A
=INDEX($E$1:$F$12,MONTH(A1),2)
E1:F12 is a table where E1-E12 is 1-12 and F1-F12 is the 'name' of the month, i.e. "October"
Now, if you use A:C to create the pivot table you can summarize the fees by month.