Formula to auto-generate next sequential number dependent on document numbering

Hi - been trying to figure this out for some time with no luck! I have scoured the forum and have not found anything that can help.

Column B is the resulting document number with the unique number (formula already inserted to combine Columns D, E, F and G) - not sure if I need Column B or C for the formula in Column G??
Column C is the resulting document number without the unique number (formula already inserted to combine Columns D, E and F) - not sure if I need Column B or C for the formula in Column G??
Column D is the document type (from a drop down menu).
Column E is the application type (from a drop down menu).
Column F is the department ID (from a drop down menu).
Column G is the unique number for the document - this is where I'd like the formula to auto-generate this number based on what is chosen from the drop down menus; document type, application type, department ID - then assign next sequential number.

Looking for a formula that would auto-generate the number in Column G so that a duplicate document number is not assigned.

Capture.JPG

Thank you all!
Ended up using Jason.b75's formula and works like a charm.
Just wondering if the formulas in Columns G and H can be tweaked so that if there is no text in Column C - the result remains blank, rather than showing the below results:
Capture.JPG

Much appreciated!

Hi avk,

Tried out your formula as well and also works!
But same thing as per my previous post, looking to only show results if there is text present.

Thank you everyone!

Try

=IF(COUNT(D2:F2)=3,TEXT(COUNTIFS(D$2:D2,D2,E$2:E2,E2,F$2:F2,F2),"000"),"")

=IF(G2<>"",D2&"-"&E2&"-"&F2&"-"&G2,"")

Note that you don't need columns B C and H, just one of them will be sufficient for the formula to work.
You could even combine the 2 formulas above and do away with column G if desired.

Hi Jason.b75,

Sorry to bother you again, but when I try the formulas you provided, it comes up blank for some reason.
I've attached the actual .xls file for you.

I'm okay with eliminating Column C and H, but B needs to remain
I basically need Column B to show the full document number, D to show the Type ID, E to show the Application ID, F to show the Department ID and G to have the auto-generated Unique Number.

Thank you so much for your time - very much appreciated!

Trina

Part of it was my mistake, I should have used COUNTA which counts text, not COUNT which only counts numbers.

Now, having seen the workbook, it becomes apparent that the opposite needs to be done and the formula needs to check that there are no blanks rather than checking that all 3 columns contain text.

The formulas in those columns, which we cannot see from a screen capture, are still classified as text because of the "" that they display, so COUNTA would still count them, which we need to avoid.

With columns C and H removed, (shifting everything else left).

In F3

=IF(COUNTBLANK(C3:E3),"",TEXT(COUNTIFS(C$3:C3,C3,D$3:D3,D3,E$3:E3,E3),"000"))

In B3

=IF(F3="","",C3&"-"&D3&"-"&E3&"-"&F3)

See attached.

Also simplified the formula in P3 for you (R3 in original file).

You are amazing!!
Thank you so much!

Probably would have been easier to attach the .xls file from the get go, but new to the forum and wasn't sure how to do it at first.

This has saved me so much time - will definitely continue using this forum in the future whenever I get stuck.

Much appreciated,
Trina