No - you would neet to include the sheet name:

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