Summing Values From Different Sheets For The Same Cell Address (EXCEL)

Question

Hello, I am trying to sum values from the first sheet to the actual sheet.

Here is what I did: 

• The Sum : =SUM('Sheet1:Here I have to put the actual sheet'!O1187)
• Find the actual sheet: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

How can I combine both in order to get my values ?

EDIT: 

I find out something but I get the result as #REF! here is the formula 

=SUM(indirect("'Sheet1:"&MID(CELL("filename",A3),FIND("]",CELL("filename",A3))+1,256)&"'!O1187"))

Do you have any idea how to fix this ?

Answer 1

Hi,

That does not work for me either.  This is probably because INDIRECT() is not a 3D function.  As an alternative, we can refer to the cumulative figure on the previous worksheet and then add the number on the current worksheet.  You may refer to my article - Worksheet formulas in a newly copied worksheet should point to the previous worksheet.

Hope this helps.