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.

If both files are open (which they need to be for INDIRECT to work), you wont need the path.

The syntax would look something like this...
=INDIRECT("["&A25&".xlsb]storage!A11")
where A25 contains the file name

Adjust the extension, sheet name and cell ref as needed