Hi friends,
I am in the middle of Creating Ids for the customers. I have somewhat
succeeded in it. I have the data as below:

Col1 Col2 Col3 Col4 Col5
Shail Deogam DEOG 0001 DEOG0001
Anupam Rathor RATH 0001 RATH0001
Shashi Deogam DEOG 0002 DEOG0002

1. The formulae I have used in the column 3 is
=UPPER(LEFT(B2,4))

2. The formulae I used at column 4 is
=IF(C3="","",IF(COUNTIF(C$2:C3,C3)1,"000"&INDEX(C $2:D3,MAX(ROW($2:2)*(C$2:D2=C3)),2)+1,IF(COUNTIF(C $2:C3,C3)=1,"0001","")))

where at the first row at the column (D2) I manually entered "0001" and
at the rest of the rows I have entered the above formula and copied it
down.

3. At the column 5 it is the concatenation of column 3 and column 5
=C2&D2

Everything is working fine until I enter another surname of the same as
just above. Say if I want to add "Ramesh Deogam" just below Shashi
Deogam, it gives me the "circular reference".

Any idea why this is happening and any idea to cure this thing.

Thanks,


Shail