Creating Monthly report

[Isara1]

I thought this was going to be a simple solution but I am stuck. I have a spreadsheet where I am entering client information for private visits. I want to then make a monthly report where I am showing and tallying up that months total. I thought an index match formula would help me but that's not working. Then I tried creating a pivot table separating every month out and that also is not working.Will doing VB be easier? Can someone please guide me to the best solution. I'm attaching a sample spreadsheet to show you what I want.
Someone please help me.

Thanks,
Isara1

[DBY]

Hello
I think a pivot is the way to go. Take a look at the attached example. It uses a slicer to select the month.

DBY

[Isara1]

Thanks for your help but I think it doesn't answer what I really need. I need to be able to make a clean Monthly report with basically the detailed information they typed in the first sheet transcribed into the Monthly Report details. The dates don't come over correctly and was the same issue I was having. I was trying to get the month on one field to use that to separate everything by month but I need to display the exact details in that month. Also you can't display more than one month at a time, I need to be able to do that. Hopefully we can get to someplace I can complete what I need.

BTW how did you do the slicer? It seems like something I could use on another spreadsheet I'm working on.

Thanks,
Michelle

[DBY]

Hello Michelle
Look at the second example file attached. It uses a couple of helper columns and VLOOKUPS to return the data and I've filled in Jan and Feb as an example. I've amend the Pivot to show dates instead of months. The slicer is inserted from the Pivot Tools>Options ribbon. You can select all, single, and multiple selections. You are not restricted to one month.

DBY

[Isara1]

This is perfect! Thank you so much. I did think about a vlookup when I was looking up index match formulas but wasn't sure how to do it. I just have one question. How can I get blanks from those formulas. For example in the Parking and Tolls columns, when I don't have any I want it to remain blank but it gives me a 0 instead. Can I make the formula do this or do I have to do an additional conditional formatting rule to make the font white if it equals 0? Please advise....

Thanks,
Isara1

[DBY]

Hi
See the new attachment. I've custom formatted the tables not to show zero values if that is what you require. Look at the formats to how each is done. It could also be done with another argument added to the VLOOKUP. I noticed I'd not formatted the columns correctly when I dragged the formulas across everything was formatted as Date! I've corrected them in this attachment.

DBY

[Isara1]

Thanks so much, I did see the format issue earlier but I was replicating the work on my original spreadsheet so I had it correctly. I knew what you meant though
I have everything the way I need it now. Thanks!

Isara1

[DBY]

Glad to have helped. Evening here so off to watch TV!