If you can use a formula in column D (or another column) in Sheet1 to provide the results you're looking for, this would work:
=IF(AND(Sheet2!$C2<>"",Sheet2!$D2<>""),"C" & RIGHT($C2,LEN($C2)-1),$C2)
Put this in D2 (or another column in row 2) on Sheet 1, and then drag down. This checks to see if the columns C and D in Sheet2 are both populated. If they are, it removes the first character from the string in column C and replaces it with a "C". If both columns in Sheet 2 are not populated, it just returns the string in column C on Sheet1.
If you cannot use a formula in a column on Sheet1, and want the results displayed in column C on Sheet1, you'll need VBA to do it. Let us know if this is the case.
Bookmarks