Automatically fill in next unused number in list

  • Apologies in advance if this should be in the VBA section but I'm hoping to accomplish with a formula (if possible but not 100% necessary if it will work without hiccups).

    Rather than explaining and being long winded, I attached the workbook I'm dealing with. When we receive Final Estimates (via fax) we have to log it as received in the green section. No big deal. Then, when an employee goes to complete one they would fill out the blue section (not necessarily in the order received). I want the "LOG#" cell to automatically fill in with the next unused number (number list is on the "Log #'s" sheet).

    Because it will autofill in when they are completed AND it needs to recognize that a number may be used already and out of order on page one (because of whenever it gets completed) I'm not sure the best way to do this (if I even can). Some sort of: If F4 contains data, then H4 inputs next chronological log number on Log #'s sheet that hasn't been used in Column H previously.

    Finally, sometimes a final is removed altogether before it gets completed because it is no longer needed. If the data or row is deleted, will it prevent the formula/code from working?

    I'd hope for something that isn't affected by a sort on any columns. Yikes!

    At this point, I'm possibly thinking too hard and there is a simpler solution, but there may not be one? HELP!

  • Hello,

    What you are asking for ... is probably feasible ...


    You are not extremely precise about the set of the rules you want to precisely follow


    You have not used the opportunity of you test file ... to provide some illustrations of your actual real-life situations ...


    Your own list of Logs does not have an indicator of which item is Already Used / Not Yet Used ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • I'm sorry--eek!. I provided several examples in the attached spreadsheet this time, and the log list WON'T have an indicator of what was used. If it needs one to make the formula work, then that's fine.

    I'm wondering if it would be simpler if I have a formula that says if H4:H2000 on the final estimate log sheet has numbers in the range of A3:A2000 on the Log #'s sheet, then that log number would turn yellow on the log #'s sheet. So people can reference the log #'s to see what number is next to be used (because it isn't highlighted).

    For example, if I enter 54100 in H4 because it is the first available log #, I'd like A2 on the Log #'s sheet to turn yellow. Continuing down, I would know to use 54101 next for cell H5 because A2 on the Log #'s sheet is highlighted. Step further, if I enter 54102 in H7 because it is the next un-highlighted log # it would then highlight 54012 on the Log #'s sheet regardless of the gap in H6 because that final wasn't completed yet.

    Does that make sense?

  • Hello,

    Would a simple conditional formatting in your Log Sheet be of any help ...???

    See attached test file ...

    In order to have the next available Log # appear automatically in column H... you should consider using an event macro ...

    Do you need this automatic update ... as soon as the user inputs a date in Column F ...???

  • You. Are. AWESOME!!! I'm over the moon! Thank you SO much!! I was already stoked about the conditional formatting version (which I tinkered with on my own but didn't quite get) and didn't think of taking it a step further and having the automatic step with the date in column F! Bless you!!!

  • Very happy to hear both solutions do please you :wink:

    Regarding the conditional formatting, there is a single formula required:

    =ISNUMBER(MATCH(A2,'Final Estimate Log'!H:H,0))

    Thanks a lot for your Thanks ...AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello,

    Following your private message ...

    If there is an additional question ... feel free to attach your latest test file ... along with an explanation about your expected result ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thank you for being so patient!

    I have some VBA code in the attached file that is meant to sort by column H (Log #) in ascending order (54100 to 54109) on OPEN. It works correctly as I F8 through the subroutine but when I save and close and re-open, the sort isn't happening. I made sure it was set to auto_open and in the general module of the workbook but my knowledge is limited as to if that's even accurate (as I'm sure you can tell :wink:). I have it saved as macro-enabled also. Can you tell me what I'm doing wrong? I want the lines to remain sorted together as it sorts. Thoughts?

  • Hello,

    In the Sheet1 module ... you can test following event macro ...

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!