Trying to create a report that shows grouped records based of an excel spreadsheet

I was hoping the forum could help me with one last piece to my puzzle.

I am currently creating a large excel spreadsheet to track Traffic Tickets for my police department and I am having an issue with previous data. I was hoping someone would be willing to lend a hand.



I need to create a short report? I was thinking of doing it via pivot table.

Basically I need it look like below

It will list each book in order from 1 through 1751 with a page break between each book.

then each Book will look like this

Book # - Date In - Date of Citation - Citation # - Def name - Officer name

Then I want it to autogenerate the numbers


Each ticket book has the following range.

####-AAA

with the 3rd and 4th digit being the of the numbers being the range of a book for example.

##01-AAA through ##25-AAA is a book

##26-AAA through ##50-AAA is a book

##51-AAA through ##75-AAA is a book

##76-AAA through ##00-AAA is a book


So even if Book 1 only had 21 tickets in needs to list all 25 just will leave the info blank?

Is this possible? I have attached a sample workbook zipped up. This workbook has no personal information.


Matt

ok I will show below

This is data from book #1 I sorted by Citation # then sorted by Book #

So then I copied all the data of Book #1

HTML Code: 

if you look at the citation list you will see that the following tickets were never turned in (For whatever reason, mistake, ruined, etc)

04
05
06
19
these are the 3rd and 4th digits of the following book

73XX-BNO

I would like the report to read like this look at the lines with N/A for the updated information.

HTML Code: 

So I would like to run a report where it would print 1 page (Per Book) just as I describe above.

Looks great? A few Questions.

Does this need to be done every time? Or can I create a macro and run it? I ask because I will be adding tickets in the future to Sheet 1?

also

Is there a way to have a page break between books?

Thanks

Matt

Not a problem

Thanks

Matt

NBMC,

Where you put that 1751 in Sheet 2 is there a way I can use the max command to have that check sheet 1 for the max Book number and replace it?


right now you have this

=MOD(INT((ROWS($A$1:$A1)-1)/25),1751)+1

cant we do something here?

Matt