More complex formulae across multiple worksheets

[Davoud]

Beyond just SUM or AVERAGE, I'm trying to perform a calculation on a summary
worksheet with data from over (35) identically formatted worksheets (within
the same workbook).

So the formula is:

=(H113+H147*(1+F76))/H115

I tried using the INDIRECT command but that seems to be beyond its scope.

So, to sum up, what I need is a way to perform the same calculation using
the distinct data in each of the (>35) identically formatted worksheets.

What other approach could I use?
Thanks in advance.

 

[Bernard Liengme]

Your simplest route is to put the formula on each worksheet in some
out-of-the-way cell; then on the summary sheet add the results with
something like =SUM(Sheet1:Sheet35!Z99)
No need to type the formula 35 times; just group the worksheets; type, press
enter and ungroup
To group: click the tab of the first sheet; hold down SHIFT and click the
tab of the last sheet
To type: just type
To ungroup: right click and sheet tab and select "Ungroup"
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
REMINDER: These newsgroups are about to die. We will all meet again at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel

 

[Don Guillett]

adapt this to your formula

Sub foreachws()
For i = 2 To ThisWorkbook.Sheets.Count
'MsgBox Sheets(i).Name
Cells(i, 1).Value = Sheets(i).Range("a2")/2
Next i
End Sub

 

[Davoud]

Thanks. Yes, we were aware of that route. One issue that I forgot to mention
is that each worksheet is submitted in protected format so that we have to
manually access each one and unprotect it before utilizing that approach.

I had hoped there was a more elegant (and hopefully straightforward)
solution that would allow us to perform the calculations directly from the
summary sheets while simply reading the data from the submitted worksheets.

It seems odd that this is such a complicated function to perform within Excel.

 

[Max]

Think you could try (again) use INDIRECT to extract it by cell refs from all
the identically structured "child" sheets, then recompose your calculations
from there

In your "master" sheet,
List all the child sheetnames in B1 across, eg: Sheet1, Sheet2,...
List all the designated cell refs in A2 down, eg your: H113, H147, F76, H115
Then place this in B2:
=IF(ISBLANK(INDIRECT("'"&B$1&"'!"&$A2)),"",INDIRECT("'"&B$1&"'!"&$A2))
Copy B2 across and fill down to populate. To the right of the populated
table, re-compose your expression " =(H113+H147*(1+F76))/H115 " in an
adjacent col. Any worth? hit the YES below

 

[Bernard Liengme]

Are you prepared to try a VBA solution?
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
REMINDER: These newsgroups are about to die. We will all meet again at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel

 

[Don Guillett]

Did you see my post??? This makes a list of the sheets and the result of the
formula. Or, do you just want the total????

adapt this to your formula
Sub foreachws()
For i = 2 To ThisWorkbook.Sheets.Count
cells(i,1)=Sheets(i).Name
Cells(i, 2).Value = Sheets(i).Range("a2")/2
Next i
End Sub

 

[Davoud]

Yes, that would get around the "Protected" issue of the individual worksheets
but I was hoping there's an approach that could be used more broadly and
universally. Manually extracting all the required data to the master
worksheet is still a little on the kludgy side.

 

[Davoud]

My knowledge of VBA is next to nothing, unfortunately.
I can assume then that there is no simple formulaic approach to this
problem. Interesting, if frustrating.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Max]

Manually extracting all the required data to the master ..

and how can that be considered manual when the formulae is doing all that
sheet work for you?

 

[steve]

=((sum(sheet1:sheet35!H113)+sum(sheet1:sheet35!H147))*(35+sum(sheet1:sheet35!F76)))/sum(sheet1:sheet35!H115)
Change "sheet1" to your first sheet name, and "sheet35" to your last sheet
name.

Regards
Steve