Automatically fill in next unused number in list

Drizz22 · Sep 17th 2019

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!

**Carim** · Sep 18th 2019

Hello,

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

BUT

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

and

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

and

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

Drizz22 · Sep 18th 2019

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?

**Carim** · Sep 18th 2019

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 ...???

**Carim** · Sep 18th 2019

Just in case you would need the ' Automatic ' version ...

Attached is your test file Version 2 ...

Hope that might help

Drizz22 · Sep 18th 2019

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!!!

**Carim** · Sep 19th 2019

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

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

Code

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

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

**Carim** · Sep 19th 2019

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:

Drizz22 · Sep 19th 2019

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?

**Carim** · Sep 20th 2019

Hello,

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

Code

Private Sub Worksheet_Activate()
Dim myRng As Range


    With ActiveSheet
        Set myRng = .Range("A3:P" & .Cells(.Rows.Count, "H").End(xlUp).Row)
    End With


    With myRng
        .Cells.Sort key1:=.Columns(8), order1:=xlAscending, Header:=xlYes
    End With
End Sub

Display More

Hope this will help

Automatically fill in next unused number in list

Participate now!

Share