+ Reply to Thread
Results 1 to 3 of 3

Referencing cell above, add next sequential number when clicking on blank cell

  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    35

    Referencing cell above, add next sequential number when clicking on blank cell

    I'm trying to create an invoice tracker and make it easy for the person who will be using it. I know that they can simply see the number above and manually enter the next number in the blank cell below, but then that wouldn't make it magical!

    I'm trying to get excel to insert the next sequential number in a specific cell that you click on, based on the cell above.
    The tricky part is that it's not a 'standard' number.
    We track our invoices based on two digit year prefix (13-) first then the number.
    eg) 13-0001

    so starting in B4 the first entry would be 13-0001. This would/could be entered manually,
    then if you click in B5 excel would input 13-0002,
    then if you click in B6 excel would input 13-0003, and so on.

    We number our invoices with four digits after the year prefix, as we never make it past 9999 invoices in a year.

    To complicate it more, I would like it so if Jan 1, 2014 we start a new file, then the numbering would have a (14-) prefix.
    We would/could manually enter 14-0001 in B4 and the rest would be automatically inputted by excel when the next blank cell below is clicked.
    So basically, the code or number format can't be restricted to (13-) only, excel would have to read the value above and then increase by one keeping the same prefix. I don't want to have to change the code or formatting every year.

    Does this make sense?

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Referencing cell above, add next sequential number when clicking on blank cell

    Hi greatwent,
    Have a look at this :
    Prefix will change automatically when year change.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-02-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Referencing cell above, add next sequential number when clicking on blank cell

    wow, that seems to work well. I enter the first invoice number of 13-0001 in B4, and then double click the blank cell below in B5 and it automatically advances to 13-0002, and so on. Awesome!
    Great job!
    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Assign a sequential number IF a cell contains text
    By fingerprints in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-22-2022, 12:43 PM
  2. Replies: 2
    Last Post: 04-11-2012, 02:06 PM
  3. Blank Cell Referencing
    By Wragg1 in forum Excel General
    Replies: 14
    Last Post: 03-05-2009, 11:49 AM
  4. How to assign a number to a cell in another sheet by clicking an object?
    By karat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2007, 04:02 AM
  5. clicking on a cell to have it add a number
    By Bryan Bloom in forum Excel General
    Replies: 6
    Last Post: 08-20-2006, 09:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1