The attached sheet shows 4 columns.

I need an Excel formula to achieve the following scenario:

If a value exists in column A (for example, the value in cell A2), place the same value in column D (D2) and continue this pattern for the subsequent rows.

If a value is empty in column A, concatenate the contents of cells B and C, Subsequently, add a continuous series of numbers for the latest 3 digits based on the preceding cell's value, which shares the same first 5 digits of colomn D. The outcome will be placed in column D.

The correc results are Yellow colored.

I need an Excel formula to achieve the following scenario:

If a value exists in column A (for example, the value in cell A2), place the same value in column D (D2) and continue this pattern for the subsequent rows.

If a value is empty in column A, concatenate the contents of cells B and C, Subsequently, add a continuous series of numbers for the latest 3 digits based on the preceding cell's value, which shares the same first 5 digits of colomn D. The outcome will be placed in column D.

The correc results are Yellow colored.