Reference all sheets in a workbook (no matter if the name of the last sheet changes)

Short version, I'm trying to reference all sheets in a workbook even when I save as with new data over the current workbook. I'm currently saving as a temp, adding a page called endpage, then saving over the old data. There has to be better - more details below.

-----------

I'm currently doing a bit of a workaround to accomplish what I'm trying to do, and I'm thinking there has to be a way to do this in the formula.
I'm working on a summary worksheet that pulls in data from a couple of reports I download each month. For one of these reports the way it comes out from the software is... inconvenient (and not something I can fix). I have a group of locations the report runs for, and each location has a name I reference by using a formula like this:
=XLOOKUP(A78,VSTACK('[worksheetlocation]Sheet1:EndPage'!$F$7),VSTACK('[worksheetlocation]Sheet1:EndPage'!F10))

Where A78 is the location name, F7 is the location on every sheet that has the building name, and F10 is the data I'm returning. This formula works. The reason I have to do this is if there is no data for a location, the sheet doesn't show. Also, the buildings don't come out in the same order every time.

That issue means that sometimes the sheet names could be Sheet1, Sheet2, Sheet3...Sheet37 then next time be Sheet1, Sheet2, Sheet3...Sheet35. So right now I download the report and name it 'TemporaryDownload', then go in and add a sheet at the end named EndPage that then lets me reference all sheets between Sheet1 and EndPage. Is there a way to simply just say I want to have the formula look at all sheets in this workbook no matter what?