If you can insert a blank row above (and hide it, if desired), then you can add formula in C2, copied down:


=IF(B2="","",IF(LEFT(B2,7)="Section",TRIM(LEFT(B2, FIND("-",B2)-1)),"REQ-"&--SUBSTITUTE(LOOKUP(2,1/(LEFT(C$1:C1,7)="Section"),C$1:C1),"Section","")& TEXT(IF(LEFT(C1,7)="Section",1,RIGHT(LOOKUP(2,1/(C$1:C1<>""),C$1:C1),3)+1),"000")))

Thank you! That is exactly what I needed. From here I can tweak it for other exceptions I'll run across.

I'd experimented with the LOOKUP function and even went so far as to think that I might use it with SUBSTITUTE to get where I needed. But I could never get there. Not even close. I was making it too complicated trying to parse out just the section number instead of just using the LEFT(7) of the cell and going from there. I follow the strategy you've used, but I'm going to need to study the solution a little more to gain a full understanding of exactly how it is working.

I was able to modify your formula (using INDIRECT) to get it to work on Row 1, but it calculates slow as molasses. And not populating B1 with a section number isn't an issue anyway, so I'll use your solution as presented.

Thank you again. I don't believe I would have gotten this on my own.

Andrew