Hi Excel Gurus,

I have a date field (dtDate) and a combobox field (cbxLocSName) (which will have a location short name) among various other fields in a data filling userform.
The format of the serial number is (Location short name + last 2 digits of the year + month + 3 digit serial number).

For ex.:

AAA1704001
AAA1704002
BBB1704001


BBB1704002

When a user changes the location short name or the date, then search the column "A" in the worksheet and find the last serial number for that particular combination of location short name, year and month
and increment it by 1 and show it another textbox (txtDocNo). So for LocSName AAA, the DocNo should show as AAA1704003

Also when the user selects a location short name for which there is no entry in the worksheet, then the DocNo should show as:

For ex.:

If the new location Short Name is CCC

Then the DocNo should show as CCC1704001

Any help on how to proceed with the above is much appreciated.

Expecting an early reply, thanks in advance.

Srinivas.