Generate serial numbers for separate sheets

[vij]

Hi All,

I have a master sheet where in Cell A9 is “Serial Number based on Cell B9 using the formula “=A9&TEXT(COUNTIF($A$9:A9,A9),"-0000000")

A 9​	B 9​
Vijay-0000001​	Vijay​
Rajiv-0000001​	Rajiv​
Suj-0000001​	Suj​
TK-0000001​	TK​
Vijay-0000002​	Vijay​
Rajiv-0000002​	Rajiv​

I have individual sheets for the names under B9 as an example under sheet Vijay the result expected is as under:

Vijay-0000001

Vijay-0000002

• While in the sheet Rajiv the result expected is :

• Rajiv-0000001

  Rajiv-0000002

 

[Gerald Higgins]

So what exactly is your question ?

And by the way, if that formula you posted is in cell A9, then I think it must actually be
=B9&TEXT(COUNTIF($B$9:B9,B9),"-0000000")
and not as you have posted.

 

[vij]

So what exactly is your question ?

And by the way, if that formula you posted is in cell A9, then I think it must actually be
=B9&TEXT(COUNTIF($B$9:B9,B9),"-0000000")
and not as you have posted.

Hi Gerald,
Yes, The formula should be what you have posted. My question is
using VLOOKUP('Main Sheet'!$B9,'Main Sheet'!$B$9:$BK$15000,1,FALSE) in the sheet Vijay in Column A:A I get all S No pertaining to Vijay.

Similarly using VLOOKUP('Main Sheet'!$B9,'Main Sheet'!$B$9:$BK$15000,1,FALSE) in the sheet Rajiv in Column A:A I get all S No pertaining to Rajiv and so on for the other sheets.

 

[Gerald Higgins]

What does this mean . . .

I get all S No pertaining to Vijay

???