How to create number sequence based off of calculated value?

[kquackenbush]

Hoping you know of a solution to my Excel/merge problem!

I am trying to figure out a way to automate our EDDM shipment forms at work because they are currently a huge waste of time to fill out manually in the current PDF format that we have. I think I have most of it figured out, but am hung up on one part that I can’t seem to find a solution to. I need a way for my merged documents to show a sequence of numbers on their corresponding pages based on a calculated value from an Excel file.

I feel like that's confusing, so I'll show you what I mean:

I created an Excel spreadsheet to enter and calculate values that I need to go on the form, and also created the subsequent Word document (a replication of the PDF that we currently use) that can merge the information gathered and calculated from the Excel spreadsheet.

Here is an example of data from the Excel spreadsheet I created:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]RouteNumber[/TD]
[TD]MailpieceQty[/TD]
[TD]ZipCode[/TD]
[TD]Date[/TD]
[TD]DeliveryType[/TD]
[TD]TotalBundles[/TD]
[TD]LooseBundleQty[/TD]
[TD]LooseBundle[/TD]
[TD]FullBundle[/TD]
[/TR]
[TR]
[TD]C002[/TD]
[TD]587[/TD]
[TD]32746[/TD]
[TD]4/26/19[/TD]
[TD]RESIDENTIAL[/TD]
[TD]6[/TD]
[TD]87[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C003[/TD]
[TD]343[/TD]
[TD]32746[/TD]
[TD]4/26/19[/TD]
[TD]RESIDENTIAL[/TD]
[TD]4[/TD]
[TD]43[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Since 99% of the time the Zip Code, Date, and Delivery Type will be the same for each EDDM shipment, I have it set so that they will auto-populate when a value is entered into the ‘A’ column from rows 2 and beyond. Columns ‘F’-‘I’ have formulas that are calculated based off of the value entered in column ‘B’ (each bundle cannot exceed 100 mail pieces).

On my Word merge form there is an area that is titled 'Total # of Bundles' where the data from the 'TotalBundles' column goes. Using the example from the data in the second row above, it is currently represented as '___of 6' on the merged form. The merged document will create one page per row of data imported from Excel, so, using the example Excel table from above, a total of two pages would be created in the merged Word document.

I would like to have a way to get sequential numbers and pages to appear in my merged file from my Excel data for the total # of bundles. So in the '___of 6' example, how do I get one sheet to be ‘1 of 6’, the next sheet to be ‘2 of 6’, the third sheet to be ‘3 of 6’ and so on in my merged file? Currently, my best solution to this problem is to print 6 copies of page one, 4 copies of page two, etc., and fill in the numbering (e.g. 1,2,3,4, etc.) by hand in the blank space.

I’ve tried Googling this problem and cannot find a solution, so I’m not sure if my search terms are off or if there is not a good way to do this between Excel and Word. Does anyone have any ideas or suggestions on how to do this??

Thanks!