Retrieve worksheet name embedded in link formula

So, I have two workbooks open that do different things, but have the same worksheet names. I want to link a column of data from the first workbook to the second workbook by worksheets with the same name. From searching, I've found this formula...

=REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"")

which will accurately display the current worksheet name. In cell C6, I've tried these two formulas to make the link from the first workbook to the second workbook...

1. ='[workbook1.xlsx]REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"")'!$AP4

2. I also placed this formula REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"") in cell A1, and it displays worksheet1 correctly, and tried these two formulas
a. ='[workbook1.xlsx]A1'!$AP4
b. ='[workbook1.xlsx]Indirect(A1)'!$AP4

The error I get every time says "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference."

I have tons of worksheets in both workbooks, so if anyone could help me, that'd be awesome.