• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Generating Invoice Numbers

dnlairways

New Member
Hello All,

I am referring to a reader question post back 20 July 2009. The question was about generating an NCF number if the customer had a Government Registration Number RCN and not using an NCF if the customer was without a RCN. I am using similar formulas to generate invoice numbers but using a client number instead of NCF. The problem is that when old invoices are removed from the database the other invoice numbers (remaining) all decrease by the number of how many are removed. i.e. remove 1 to 4 and the next becomes 1 and so forth. This happens on both the reader question and in my spreadsheet.

Old invoices will have to be removed from the database from time to time as these will slow the array formulas used in the lookup. (they are slow enough now!)

Thanks in advance.
Dave The-slave
 
Hi ,

If you use formulae , Excel will always recalculate the formulae when ever you add / delete columns , rows.

How are you removing old invoices ? Are you merely clearing the cells or are you deleting entire rows ?

Narayan
 
Thanks Narayan for your prompt reply,

The old invoices are removed by cut and paste into a different tab. I am using a 30 column table (limited by MS user form) beside which is a 5 column table, which duplicates 2 columns from the larger table (client name & date) and where the invoice number is stored.

Orders are taken some days before (including delivery date) and entered via the user form. When the delivery date matches the current date the data is listed in a drop down menu with client and invoice number from which the user will select which invoice to print.

Thanks
Dave
 
Hi ,

Doing a cut and paste will change the dependent formulae or convert them to error values.

Have you tried doing a copy and paste , and then clearing the data cells ?

Narayan
 
Hello and thanks for taking the time to consider my problem, I just had a few days off enjoying the holidays and the heat and the rain...
Now I have tried to upload my file but I keep getting an error.
I will try from another computer.
 

Attachments

  • dnlairways invoice question 1st rev.xlsx
    86.8 KB · Views: 4
  • dnlairways invoice question 1st rev.xlsx
    86.8 KB · Views: 0
Hi ,

See if this is OK. I have put in a different formula for the invoice number.

Narayan
 

Attachments

  • dnlairways invoice question 1st rev.xlsx
    89.5 KB · Views: 12
Back
Top