How to create number sequence based off of calculated value?

kquackenbush

New Member
Joined
May 6, 2019
Messages
1
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:

RouteNumberMailpieceQtyZipCodeDateDeliveryTypeTotalBundlesLooseBundleQtyLooseBundleFullBundle
C002587327464/26/19RESIDENTIAL68715
C003343327464/26/19RESIDENTIAL44313

<tbody>
</tbody>

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!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,123,252
Messages
5,600,543
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top