+ Reply to Thread
Results 1 to 17 of 17

automated ref number generation

  1. #1
    Registered User
    Join Date
    10-15-2008
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit
    Posts
    24

    automated ref number generation

    hi, what im trying to achieve is generating ref numbers automatically.

    I have attached an example.

    In Cell A if the user choose yes it gives a ref number. The trouble im having is that say in cell A5 a Yes is choosen at a later date, it will throw the numbers out of order and the ref numbers get mixed up, there any way of stopping this from happening or any function that prevents this from happening

    A pointer in the right direction would be appreciated

    Sal
    Attached Files Attached Files

  2. #2
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: automated ref number generation

    Are you looking to do this with a formula or in a macro?

    A little more info on what you're trying to accomplish would help too. There are a lot of really, really smart people here, and the more they know, the more likely you are to get your solution!
    Last edited by JP Romano; 10-07-2009 at 04:35 PM. Reason: added

  3. #3
    Registered User
    Join Date
    10-15-2008
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit
    Posts
    24

    Re: automated ref number generation

    Apologies for the lack of description.

    What i need to do this everytime a user says yes in the field a ref number is generated and this works fine if the user is always sure. But now and again the user will go back and change his mind from a no to yes which throws off the numbering. For example

    Generate Change Request form Yes/No Index Number

    Yes 1
    No
    Yes 2

    after the user changes the no to yes, the index numbers change which mixes up the change request forms, so when we come 2 look for them they are never correct.

    Generate Change Request form Yes/No Index Number

    Yes 1
    Yes 2
    Yes 3



    No to sure if this has clarified my issue.


    Thank you


    Sal

  4. #4
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: automated ref number generation

    Thanks for the info. Is it necessary for the reference items to be consecutive? Will there be an expected number of lines in which the users can/will enter the Yes or No?

  5. #5
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: automated ref number generation

    You might consider something like this:

    = IF(A3="Yes",ROW(A3),"")

    That will simply return the row number the Yes is on - it'll never change
    ... no math required!

  6. #6
    Registered User
    Join Date
    10-15-2008
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit
    Posts
    24

    Re: automated ref number generation

    Quote Originally Posted by JP Romano View Post
    Thanks for the info. Is it necessary for the reference items to be consecutive? Will there be an expected number of lines in which the users can/will enter the Yes or No?

    No and No

    This will go on for however long they intend to keep this system.

    Thanks

    Sal

  7. #7
    Registered User
    Join Date
    10-15-2008
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit
    Posts
    24

    Re: automated ref number generation

    Quote Originally Posted by JP Romano View Post
    You might consider something like this:

    = IF(A3="Yes",ROW(A3),"")

    That will simply return the row number the Yes is on - it'll never change
    ... no math required!
    Thanks for the solution.

    Ok im going to be awkard now and say what if it was neccessary for the numbers to be consecutive. How would you go about doing that?

    Thanks,

    Sal

  8. #8
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: automated ref number generation

    Not awkward at all. Unfortunately I'm not quite sure how that would be done by a formula. But, you could always create a button and link this macro to it


    This will, basically, set B2 to 0, then anytime it encounters a "Yes" in column A, add 1 to the previous value, even if it's not on the adjacent line.

    Hope that helps!

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-15-2008
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit
    Posts
    24

    Re: automated ref number generation

    Quote Originally Posted by JP Romano View Post
    Not awkward at all. Unfortunately I'm not quite sure how that would be done by a formula. But, you could always create a button and link this macro to it


    This will, basically, set B2 to 0, then anytime it encounters a "Yes" in column A, add 1 to the previous value, even if it's not on the adjacent line.

    Hope that helps!

    Please Login or Register  to view this content.
    Hi, thanks for that, but that didnt quite work, ive attached a workbook on what its done.

    Thanks

    Sal
    Attached Files Attached Files

  10. #10
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: automated ref number generation

    Hi...we're getting there.
    Couple of things...

    - Looks like you change the range to select from "B2" to "B2:B5000" - you don't need to do that because the loop is only applied to the active cells.

    - Your original showed "Yes" as the trigger, but your second sheet shows "yes" (not capitalized). The code below will use either.

    - You added values to columns D and E... this macro doesn't touch those.

    Try with this code instead.

    Please Login or Register  to view this content.
    Let me know how you do with it....

  11. #11
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: automated ref number generation

    Here's the file with the code...
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    10-15-2008
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit
    Posts
    24

    Re: automated ref number generation

    JP,

    Apologies about that.

    It seems to be looking good.

    Thank you

    Sal
    Last edited by shg; 10-08-2009 at 11:26 AM. Reason: deleted spurious quote

  13. #13
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: automated ref number generation

    No problem at all...glad to help.
    Please be sure to mark this post SOLVED when you have a minute.

  14. #14
    Registered User
    Join Date
    10-15-2008
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit
    Posts
    24

    Re: automated ref number generation

    Quote Originally Posted by JP Romano View Post
    No problem at all...glad to help.
    Please be sure to mark this post SOLVED when you have a minute.
    Hi,

    its not quite there yet. Whats happening is when i change a no to yes, it still throws off the numbering. At B12 the index number is 7, but it changes to a at E11 to a 7.

    I think what im after is for it to look at the largest number in the range and increment it by one, everytime the user answers Yes.

    I apologies if i wasnt clear before.

    Thanks

    Sal

  15. #15
    Registered User
    Join Date
    10-15-2008
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit
    Posts
    24

    Re: automated ref number generation

    forgot to attach the file
    Attached Files Attached Files

  16. #16
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: automated ref number generation

    Hi... I think that's because you assigned a macro designed to work with Columns A and B to colums D and E. What you have to do (which is in the attached), is add a second macro for the next set of columns specifically targeting the next column.

    Question though - if you're going to have multiple columns like this, should the ref numbers start at 1 in each, or should they be continuous? How many sets of columns are you going to have, ultimately?
    Attached Files Attached Files

  17. #17
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: automated ref number generation

    If you're looking for a continuation of ref numbers between the columns, use this:


    Please Login or Register  to view this content.
    When you click the button on the left, the macro will run as normal. If you have data in the first cell in your range in column D, it'll continue on it's own using the last number from Column B (+1) as the ref number for the first Yes value in column D.

    Does that cover it?

+ 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