+ Reply to Thread
Results 1 to 3 of 3

Function to generate next available number

  1. #1
    Registered User
    Join Date
    12-19-2006
    Posts
    2

    Function to generate next available number

    Hi guys,

    I am trying to set up a function that automatically generates the next unique available number in the same column but not in row order.

    To activate the automatic calculation we use the validation function in an adjacent column (cell).

    In few words...if (cell non blank), then (find larget number in column + 1)

    OR......if (cell non blank), then (show next available unique number)


    Product Number

    AAA 7001
    BBB 7003
    EFG 7002
    PKO ?????? (it should display 7004)

    Please note: The order we activate the products is not sequential.

    Thank you

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    As far as I know you can not do it if your product numbe is a combination of letters & numbers without using a helper column

    The helper column will have a list of the numbers

    7001
    7003
    7002

    You could then use the Max formula to get the largest number.

    The other way is to use a UserDefinedFunction like this one

    Place this in a function code in a normal module sheet.

    Please Login or Register  to view this content.
    Then on your worksheet use this formula

    =FindMaxNumber(C1:C23) - change range of cells to suit
    Last edited by VBA Noob; 12-19-2006 at 05:13 PM.

  3. #3
    Registered User
    Join Date
    12-19-2006
    Posts
    2
    Thanks for your responce.

    I already tried the max function but I get a circular reference because in the max range I include the cell that needs the new number.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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