Create Order Reference which resets each day

I'm building a order / sales / invoice spreadsheet, which revolves around an 'Order Reference'. This is created by TEXT function combining a date and a number:

=TEXT(D3,"ddmmyy")&G3

Where D3 is the date and G3, an integer starting at 1

For each day, the order ref. should start at 1 and automatically increment for each order added (and logged on a separate sheet) for a particular date. If a new date is entered, the order reference should start at 1 again.

As all orders are logged on a separate sheet, I need validation to check that the order refererence number is sequential and that no order reference is duplicated.

Any help on the code would be appreciated.

Mark

It has a lot to do with how the entries are being added to this sheet in question, manually of via a macro.
a dummy workbook would be helpful.
have a read of this you may find it helpful if manually entered