I need to sum a common range across duplicate sheets with the same format, but different content to total on each sheet, for a dynamic amount of sheets. This is how I've set it up. It works on it's one, but once linked to other sheets it goes #Value!, Perhaps this can be fixed, perhaps you gents/ladies have better way of calculating this.

I have a Workbook template calculation sheet for products, lets say cars. Each project has 1 or more cares, and each car may have different details. So a workbook I can have several sheets for several cars. E.g. 3 cars = 3 sheets. Each car sheet get a numeric name, e.g. 1, 2 & 3. There are more sheets in the workbook containing reference data. These sheet are not numeric. Each car sheet needs a sum product of a range of numbers common on each car sheets.
Because upfront I don't know how many cars there will be in a project my template is setup with a piece of code that simple checks each sheet and count only those that are numeric.

Please Login or Register  to view this content.

sheets named 1,2&3 all contain the same data for the sake of the example, but could be different data. I need to do a sumproduct() for all numeric sheets in the workbook, for a given range. This range could be larger or smaller per project, but always equal for all cars within the project. In this case B2:D2. The sum is returned in B4. (0.3 + 0 + 2) + (0.3 + 0 + 2) + (0.3 + 0 + 2) = 6.9 This is calculated using the following equation: =SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1"&":"&QtyCars()))&"'!"&ADDRESS(ROW(B2),COLUMN(B2))&":"&ADDRESS(ROW(D2),COLUMN(D2))),">0"))
QtyCars() is function made to calculate the total number of numeric sheet names in the workbook.

Sheet 3(2) contains the same data as sheet 2, say an alternate offer for care 3. This is not numeric and is therefore correctly not taken into consideration. But it does included that total number of 6.9 like it should.

All works like a charm, but... I have another workbook template setup to work with the above described calculation. It is build dynamically to pull data from any workbook based on the above described template. But it requires both workbooks to open at the same time. The moment I switch to the other workbook, the my calculation answer in B4 goes #Value!. This renders the readout to the other sheet also to !#Value. It seems to also happen upon open of just the workbook described above. F9 will solve the problem, but only in the calculations sheet. the moment I want to look into the other sheet, it goes #Value again. So I can't use my other sheet. The link worked fine before I added the above described sumproduct() so I guess it has to do with that. But for all i know it may be it could be something else.
If it helps: the organisation I work for stores everything on SharePoint/Office 365. I need to click through Edit Workbook upon open, allow macros', continue to update equations.
OS: Window 10