Tweak formula to work again after changing worksheet

I had this project finished but there were complaints so I had to change it. The problem is now the formulae I had no longer work. If you open the workbook you will see the second and third tab reading "Sequoia" and "Sequoia Complete" respectively (I will be making a Dawn and Coast sheet as well when I figure out the formulae). The end result I am looking for can be seen on the "Sequoia Complete" tab. I would like to reference the Census Sheet for all data as that will be the one sheet that is edited.

Here is the formula for grabbing the name off of Census Sheet: =IFERROR(INDEX('Census Sheet'!$C$3:$C$24,SMALL(IF('Census Sheet'!$N$3:$N$24="C",(ROW($M$3:$M$24)-ROW($M$3))+1,""),ROWS($1:1)),1),"")
Here is the formula to flip names: =TRIM(RIGHT(SUBSTITUTE(B4," ",REPT(" ",255)),255))&", "&LEFT(B4, LEN(B4)-LEN(TRIM(RIGHT(SUBSTITUTE(B4," ",REPT(" ",255)),255)))-1)
Here is the formula to alphabetize by last name: =LOOKUP(2,1/FREQUENCY(ROWS($1:1),1+COUNTIF($A$4:$A$15,"<"&$A$4:$A$15)),$B$4:$B$15)
Here is the formula to populate as first name, last initial: =LEFT(C4,FIND(" ",C4))&" "&MID(C4,FIND(" ",C4)*1,2)

I do realize the columns and rows no longer match up, but after I could not get the first formula to work I just stopped and came here before I really did some damage.

I currently have a hidden column utilizing the last formula, but it is useless to me now. I can have hidden columns although I prefer them to the right of the table.

forum1.xlsm

Nevermind...I got it...I did not think I would be able to make it work.Round2.xlsm