Pulling data through based on the month

Question

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

I don't quite get the problem here. Can you use some example values, pictures as well maybe of how it looks like and how you want it to look like? — Jerry

12V_man 12V_man · Accepted Answer · 2015-10-02T18:35:26

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.

Pulling data through based on the month

1 Answers