Unique Invoice No.

  • Thread starter Thread starter kaddrison
  • Start date Start date
K

kaddrison

Hi

I'm doing a city and guilds course in spreadsheets and having a problem
on a question

It an invoice for a company and the question states that it should have
a unique invoice number

Any ideas have asked in class but they say it can not be done but its a
question and I guess it must be able to be done!

Thanks for reading and any help much appreciated
 
Hi
depends at which point you want to create this number. One way:
1. Create a new workbook or open an existing workbook. Design
everything as you like, etc. You will dedicate a single cell for your
invoice number. Lets say a an assumption it is cell A1 on the first
sheet (name 'sheet1').
2. Save this Excel sheet.
3. Right click on the Excel symbol left to your menu entry 'File'.
Choose 'Code' in the context menu. The VBA editor will open.
4. Paste the following code into the VBA editor:
Private Sub Workbook_Open()
Worksheets("Sheet1").Select
Range("A1").Value = Range("A1") + 1
End Sub
5. Close the VBA editor, save the Excel file and close it.

This will increment A1 each time you open this workbook. A similar
result could be achieved using the beforeprint event 8increments the
number each time you print the worksheet)
 
Thanks for the reply but at the standard the course its at the is no wa
the answer can be vba - I have used vb but the course is at a muc
lower level - either I 'm missing something or they are correct - a
this level it can not be don
 
I've used a combination of 'customer number' plus the 'year and month' for
my invoicing.

eg: company xyz has customer number 8032
today's date 04/02/16

invoice number would be 80320402.

I've limited myself to 8 characters for file naming(OK I'm old.).

You can expand this to include the day of the month if required.

giving 8032040216

hope this helps

jay
 
Hi
AFAIK without using VBA there is no way to create an automatic sequence
number for invoices. so they're probably right
 
Hi kaddrison!

If you don't need the numbers in consecutive sequence but nevertheless
are in sequence.

=--TEXT(ROUND(NOW()*1000000,0),"####")
Having got your number you will need to use:
Copy
Edit > Paste Special > Values
OK

Or:
=--TEXT(NOW(),"yyyymmddhhmmss")
Having got your number you will need to use:
Copy
Edit > Paste Special > Values
OK

Returns a number. Remove the -- and you have text.

Limitations are

1. That numbers must all be generated on the same computer because
there is a possibility that two people could generate the number at
the same time on the same day.

2. You can't copy down these formulas to generate a batch of numbers
but have to do it one at a time.

But there may be other non-vba ways.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top