# Summing Values from Different Sheets for the Same Cell Address

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 ?
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EngineerCommented:
=sum(indirect(...yourstring...))
0
EngineerCommented:
Probably

=SUM(indirect("'Sheet1:"&MID(CELL("filename",A3),FIND("]",CELL("filename",A3))+1,256)&"'!O1187"))
0
Author Commented:
I Get this : #REF!
0
EngineerCommented:
0
Commented:
I don't think INDIRECT can return a 3D array, so you will likely need to look at a VBA solution, such as this UDF...

``````Public Function SumSheets(CellAddress As String, StartSheet As String, Optional EndSheet As String = "") As Double

Dim i As Integer, IndexStart As Integer, IndexEnd As Integer, sum As Double

If EndSheet = "" Then
IndexEnd = Application.Caller.Parent.Index
Else
IndexEnd = Worksheets(EndSheet).Index
End If

IndexStart = Worksheets(StartSheet).Index

For i = IndexStart To IndexEnd
Next

SumSheets = sum

End Function
``````

Note that the EndSheet is Optional, so if omitted, it will default to the sheet the formula is on.

Example usage would be...

=SumSheets("O1187", "Sheet1")
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
But when I put =SUM('Sheet1:Sheet8'!O1187) it works but why when I put =SUM(indirect("'Sheet1:"&MID(CELL("filename",A3),FIND("]",CELL("filename",A3))+1,256)&"'!O1187"))
I have the #REF error ?
0
Commented:
You can't use INDIRECT to return a range across multiple worksheets. You'll need VBA, as I suggested above.
0
Author Commented:
Did you try the VBA that you gave me ? Because it is not working also in mine...
0
Commented:
Of course I tried it and confirmed that it works. What do you mean by "not working"?
0
Author Commented:
It is telling me that the formula that I wrote is having error...
when I write this =SumSheets("O1187", "Sheet1")
0
Commented:
What's the error?
0
Author Commented:
When I write the formula like you told me... Excel is giving me a pop up and inside it is written :" the formula that you write is containing an error" but it is not telling me what is the error...
0
Commented:
Have you pasted the code into a regular module?

Attached is an example workbook showing the working formula.
SumSheets.xlsm
0
Author Commented:
In your example it is working but in mine it is not... Strange ! Thank you ! Also, in my workbook, I have many sheets but I need to use only specific sheets like (J1,J2,J3,.... till the actual sheet) maybe it is because of this the formula is not working.
Any idea how to deal with it ?
0
Commented:
My UDF will sum the cell in every worksheet until the one containing the formula. If you wish to skip some worksheets, we can add another optional parameter to specify which sheets to skip.

The sheets to skip are passed as a comma separated string value. Example usage would be...

=sumsheets("A1", "Sheet1", , "Sheet5,Sheet8,Sheet9")

``````Public Function SumSheets(CellAddress As String, StartSheet As String, Optional EndSheet As String = "", Optional SkipSheets As String = "") As Double

Dim i As Integer, IndexStart As Integer, IndexEnd As Integer, sum As Double, skips As String

If EndSheet = "" Then
IndexEnd = Application.Caller.Parent.Index
Else
IndexEnd = Worksheets(EndSheet).Index
End If

IndexStart = Worksheets(StartSheet).Index

skips = SkipSheets
If Left(skips, 1) <> "," Then skips = "," & skips
If Right(skips, 1) <> "," Then skips = skips & ","

For i = IndexStart To IndexEnd
If InStr(skips, "," & Worksheets(i).Name & ",") = 0 Then
End If
Next

SumSheets = sum

End Function
``````
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.