Not like that.

You'd need

=INDIRECT("'Sheet1'!AG"&MID(B7,FIND("$",B7,2),7))