I went through the evaluation process but am definitely still confused.
=IF(C$1="","",C$1&TEXT(MAX(IF(LEFT(Sheet1!A$1:A$22)=C$1,0+MID(Sheet1!A$1:A$22,2,4)))+1,MID("0000",1+(C$1<>"B"),4)))
I understand if C1 is blank, return a blank. not sure the C1&Text...perhaps stating to look for text in C1..., The Max is finding the next number, the If is stating another condition (I am assuming the matching between C1 and column A in Sheet1). I don't fully understand the Left function as I have never used it before but I think it is isolating the left letter in the column (example G) and saying if it equals C1....but after that I get lost.... Never used MID before either and definitely don't understand the C1<>"B" or why do we use absolute values (cntrl+Shift+enter).
Anyway I doubt you have time to elaborate. Thank you for providing the formula, I hope to fully understand this all one day.