Referring to a file path contained in a cell rather than updating an entire sheet

Come into an environment where a worksheet is essentially copied into another one which pulls and formats the data correctly for uploading. Issue is that every half year the worksheet that is copied changes name so I have to update tens of thousands of rows with the new spreadsheet name.

I want to record the name of the spreadsheet I'm copying into one cell and then refer to that so I only need to update one cell vs tens of thousands. Is there a way to do that? Things I've tried have come up with errors and my google-fu is unfortunately not providing this specific answer.

As an example it's currently: filepath[sheetname]A1 and so on in every cell

But I want to record 'filepath[sheetname]' to a cell instead and reference that (say cell B1).