invoice numbers

Topic

New Reply

Hi All,

I’ve seen numerous posts, articles on using sequential numbers for things like invoices – some go back to the beginning of the century.

However, my needs are slightly different:
– the document to be numbered is actually a directory – several receipts on a page (3 or 4, haven’t decided yet) that have to be numbered
– can’t use VBA, which is the basis of all the solutions I saw, since the person is using Office 2008 on a MAC

Like those other solutions, each receipt will be numbered but all the receipts will be in one “document.”

When the next batch of receipts is to be generated, numbering should pick up with the next number after the last one in the previous document.

If necessary, a “log” file could be created (but without VBA) containing the sequence numbers in each document or with just the last sequence number of a document.

I’m imagining some manual steps like copy-paste to make up for the lack of VBA.

Any thoughts?

TIA

Fred

Reply | Quote

Replies

In that case, why not just use paragraph list numbering to generate the numbers? Any time you want to create a new receipt, simply add a new row to the table and Word will give that row a new number. Personally, I’d be inclined to have a spare page of receipts (perhaps stored as a Quick part) I could just add to the end of the table so I wouldn’t have to clear any existing data – it would also provide more flexibility with the receipt layout.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul,

I’m confused about what you’re suggesting.

simply add a new row to the table and Word will give that row a new number

I do understand that if I start a table and number the row using Word’s numbering feature that adding a new row to the table results in a number in that new row with the next sequential number. But it’s not clear to me what the table is and how the number in the row relates to the receipt #.

Let me be a little more explicit about the setup.

My friend has a “data base” in Excel (MAC 2008) with one row per student in her school. The row contains lots of information about the student, including the monthly tuition and whether the tuition is handled as an automatic payment against the parent’s credit card. The tuition is the same Oct-Apr with a different amount for May (don’t ask), so there are actually 2 cols for these (one for Oct-Apr and another for May); there’s another col to indicate whether tuition is billed automatically. Since automatic payments are handled by the bank at the beginning of the month (about 40 or so), she wants to generate a receipt for the parents after seeing the automatic payments have all been done. The set of parents doing automatic billing doesn’t change much, if at all, from month to month.

What I was going to do was set up a mail merge from the Excel workbook to a Word document in the form of a table that looks like her receipt (the size would allow 3 or 4 to a page). The table would have a space for a receipt #. The number should increment for each receipt printed that month, so say 1-40 for Jan over about 10 pages. The last step of the merge could be to a document so she can make any final edits to the document.

The following month, she needs the receipts numbered 41-80; then 81-120; etc.

I’m not sure if I can do the merge directly from her spreadsheet so I may have to create another sheet in the workbook with just the appropriate info for the receipts. I could set up a col for receipt #, which can be changed easily from month to month. I was thinking this might be needed to generate the receipt numbers since it’s unclear how to do this in Word w/o VBA.

Comments?

BTW: I think there’s a bug in the “Go Advanced” button. This is the 2nd time it has happened to me in the past few months. I click on Go Advanced to see what I’ve done (the quotes) and I’m logged out. Luckily, this time, I had copied the contents of my msg before hitting Go Advanced so I just pasted the contents back in and added this paragraph.

Thanks again.

Fred

Reply | Quote

It would have been helpful had you said in your first post that this is for a mailmerge. You made no mention of that, thus giving the impression it was an entirely manual process.

For a mailmerge, you can just merge the required records and use the merge record # for the receipt #. If you’re using the same records, but with different fields each time, you could use field coding to test which field you’re merging from and add an appropriate constant to the #s so you can differentiate them.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Sorry Paul.

When I said

However, my needs are slightly different:
– the document to be numbered is actually a directory – several receipts on a page (3 or 4, haven’t decided yet) that have to be numbered

I thought that saying in a Word forum that this was a “directory” implied a mail-merge – so my bad.

As to your last paragraph, there would be 7 monthly merges (Oct-April) for all the same students. That’s a general statement since a parent might elect to go to automatic payments in the middle of the school year (rarely do they go off auto payment). Other than that, the records to be merged would be identical (same parent, same tuition); the only things that would differ is the date of the receipt, the “Note” for the purpose of the receipt (“October tuition” on October’s receipt, etc.), and the receipt number. As mentioned above, the May tuition is different from any other month. I’m thinking that the date and purpose of the receipt could be handled in Word; it was the sequence # that I was concerned about generating.

If you’re using the same records, but with different fields each time, you could use field coding to test which field you’re merging from

Not sure I follow; per above, everything is the same with just the different. Not sure what you meant by “use field coding to test which field…”

Fred

Reply | Quote

Without knowing the exact structure of your workbook, it’s difficult to give specific advice. Can you attach a workbook to a post with some representative data (delete/obfuscate anything sensitive)? You can do this via the paperclip symbol on the ‘Go Advanced’ tab at the bottom of this screen (notwithstanding your reported experience with it, which I haven’t seen).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul,

I’ve been doing some work on what I need and am attaching it.

My approach, in the absence of VBA on the MAC for Office 2008, is to have a row for each receipt to be generated. You can ignore pretty much all the columns since they are things needed on the receipt. The key thing is the generation of invoice #s.

As said in my previous post, the same set of receipts “pretty much” need to be generated every month. There may be exceptions if a parent decides to go on the program of auto billing to his/her credit card (add a row to the spreadsheet) or if they can’t be billed that month (card expired but they forgot to tell the school or the bank, so the auto payment does NOT go thru – NO receipt).

Invoice #s need to be generated each time the mail merge is done each month. Oct might have #s 100-139 but Nov might only have 140-175 since a few auto payments didn’t go thru (the school owner gets emails from the bank saying success or not and can set col A to NO for the failures).

I have not designed the directory yet. But I’m envisioning some things being done in Word: the date for the receipt (always the first of the month), purpose of payment being that month’s tuition. The receipts will always be generated within a day or two of the 1st of the month when the bank does the autopay.

You’ll notice in the spreadsheet that there’s a place at the far right (AN2 but I’ll probably move this to another sheet) where manually the owner has to enter the first invoice # to be used the following month – add 1 to the last # generated this month. This is what I was hoping to avoid using some of the approaches that I’ve seen elsewhere but they all need VBA and it’s not clear they can be used in a directory.

I may have to repost here for some of the things mentioned in above. I’m assuming in Word that I can get “today’s date” and parse it to create a date of the first (ie, even if the mail-merge is done Jan 3, 2016, the receipt should be dated Jan 1, 2016); from the same date, I need to be able to create “for January’s tuition”. I’ll be checking those tutorials you posted.

Of course, I’ll skip those records that have a NO in the Print Rcpt field (and I may come back on this too).

Thanks.

Fred

Reply | Quote

Since your workbook already has Receipt #s in column B, your mailmerge can use those. If you also had receipt dates, you could use a SKIPIF field to ignore records outside a given date range (as well as those lacking receipts). Failing that, when you merge, you can specify the record range to merge, combined with a SKIPIF field to ignore records lacking receipts.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks Paul.

I had to put the Receipt #s in the workbook bcs it seemed like there was no way to generate them in Word w/o VBA and for a directory, as first asked. So for now, it looks like I’ll have to handle it this way.

I understand what you’re saying about the dates. However, the plan, for now, is NOT to create new records every month with the same info but just a different date and receipt number. The same rows will be reused but new receipt numbers generated just by changing that first receipt number in AM2. The only other thing that will change is col A if the autopay did not go thru for some reason; then the corresponding row in col A will have a NO. So in Word, I’ll just put into the “Edit Data” step of mail merge just to filter those records that do not have NO (similar to what the workbook is doing when generating the Invoice #).

But you do raise an interesting point. Instead of re-using the rows with a different Invoice #, why not just keep adding the same rows every month and let Excel generate the invoice #s for the new rows? That way, there will be 1 file with all the data. In the merge process, I can set up something to filter out the old rows from previous months.

Will give that some thought.

Thanks for the help.

Fred

Reply | Quote

I had to put the Receipt #s in the workbook bcs it seemed like there was no way to generate them in Word w/o VBA and for a directory, as first asked. So for now, it looks like I’ll have to handle it this way.

generating sequential numbers isn’t at all difficult for a mailmerge. As I said in post #4:

For a mailmerge, you can just merge the required records and use the merge record # for the receipt #.

A bit of field trivial maths in the mailmerge main document can add a constant to that, if you want. That said, keeping the receipt #s in the workbook makes more sense, as you’d otherwise have no other record of what they are – I’m sure you wouldn’t want to have to re-run the mailmerge or dig out the old outputs just to find that out anytime there’s a query.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Aha!

I didn’t quite get what you had meant in post #4 about the merge record #s. And yes, she wants the invoice #s to start with 100. I’ve done some field math in the past, so I know this can be done.

And yes, she does need to keep a copy of the receipts (she gets receipt books from some company that has been supplying them for years and years and they generate the next sequence #s when she orders more books). So she’ll either make a copy of the printed page before cutting it up or print 2 copies. Parents some times call in and ask about receipt #101 or whatever.

The one thing that was clear and I guess I’d have to go back to what you wrote earlier (the table and adding a row) is how to pick up with the sequence the following month. If we kept all the receipts in the workbook for all months, it would be easy since the rows for Feb would just pick up after the last # for Jan. Then we’d just need a way, as you said, to skip all the rows for old receipts.

One question: when skipping a record during the merge, does the next merged record get the next merge record # or does the skipped record take up that number as the next record from the data source even tho it gets skipped?

Thanks for all the help.

Fred

Reply | Quote

Since the simplest place to keep a record of the receipts is in the workbook, which is where you now have them, it seems to make sense to use them from there. Then if someone wants to know about a particular payment, the workbook has all the details and, if needs be, the receipt # that you can look up in the printout (rather than having to trawl through the receipts to find which one matches a particular payment, just so you can get the number).

Word has two counter fields for merges – MERGESEQ and MERGEREC. The former returns which item in the merge series is being processed whilst the latter returns which record is being processed. See:
https://support.office.com/en-gb/article/Field-codes-MergeSeq-field-f84ff007-79fe-4378-a535-02e53512effd?ui=en-US&rs=en-GB&ad=GB
https://support.office.com/en-us/article/Field-codes-MergeRec-field-6cf91d88-e7ee-4603-97d2-3f703a1d2d18?CorrelationId=93b5199c-29ff-494a-a635-c63d1a8d205e&ui=en-US&rs=en-US&ad=US
Note the comments in the latter article about the effects of filtering and sorting, plus how it might be combined with a PRINTDATE field to generate unique numbers.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote