Vlookup or Match and Continuous Numbering

Question

I am having a difficult time figuring this out and your help is greatly appreciated!

If there is the same name across multiple sheets, I would like the the number first assigned to them to populate and the remaining numbers to be in numerical order throughout the remaining sheets.

Below is sheet 1. C9 has a star near the name because it is a duplicate in sheet 2 (to follow)

Below is sheet 2. C9 is the duplicate name from sheet 1. I need the numbering for sheet 1 to remain as is and sheet 2 as 1, 8, 9, 10, 11, etc.

I am up for 2 options:

1. Manually enter 1 in cell B9. When I currently do this on sheet 2, the numbers start over again from 1 as opposed to the MAX number from sheet 1.

2. Automatically populate number when there is a name match. I have tried vlookup and match with my current formulas and they do not work with continuous numbering.

Below are my current formulas:

Cell B9, sheet 2

=MAX('Sheet 1'!B9:B66)+IF(ISTEXT(C9),1,"")

Cell B10:B66, sheet 2

=IF(ISTEXT(C10),IF(OR(ROW()=9,IF(ROW()=9,TRUE,MAX(INDIRECT("B9:B"&ROW()-1))=0)),MAX(INDIRECT("Sheet"&RIGHT(REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""),LEN(REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""))-5)*1-1&"!C:C"))+1,MAX(INDIRECT("B9:B"&ROW()-1))+1),"")

I really appreciate your time and help!!

Answer 1

Assuming B8 in Sheet2 is not numeric, names do not repeat within any one sheet and only two sheets are involved, I suggest in B9 of Sheet2 and copied down to suit:

=IFERROR(INDEX(Sheet1!B:B,MATCH(C9,Sheet1!C:C,0)),MAX(Sheet1!B:B,B$8:B8)+1)