# Thread: Sum of calculation AND worksheet name that it appears on.

1. ## Sum of calculation AND worksheet name that it appears on.

Hi to all,

This is my problem, I have a workbook with several sheets in it, one of them is a summary sheet, in which I have inserted a calculation. Basically asking for it to tell me which sheet has the highest figure in the D4 cell. It gives me the figure, but I would also like it to show me the name of the worksheet that the answer came from.

Any help on this one please.

Kind regards to all

Steve - Southampton UK

2. You could create an intermediate list in a sheeto of the D4 values from each of the sheets and then do a lookup on this list to find the maximum and the sheet associated with it...

Steve

3. Originally Posted by sdckapr
You could create an intermediate list in a sheeto of the D4 values from each of the sheets and then do a lookup on this list to find the maximum and the sheet associated with it...

Steve

I have rewritten this in another way, I think I might be confusing the issue ;(

I have this formula in a cell =MAX(ABJ:Williams!D1) giving the largest figure in cell D1 across multiple sheets, this works fine, however I would like to also know the name of the worksheet it got the answer from?

Hope you can still help.

Regards Steve

4. My answer remains the same.

I presumed you were doing a 3D cell reference. The lookup functions are not 3D capable, so the lookup must all be on the same sheet.

Steve

5. I'm with Steve. I tried to create a UDF to solve the problem but Ranges in VBA are also not 3D compatible, e.g.
For each oCell in rng...

6. Originally Posted by RetiredGeek
I'm with Steve. I tried to create a UDF to solve the problem but Ranges in VBA are also not 3D compatible, e.g.
For each oCell in rng...
Thanks very much indeed for your help..

regards

Steve

7. Hi,

See my attached file, I created a lookup listing of all the sheet names with a link to the relevant cell to find the maximum in - I then used the Max function on this list then used a lookup function against the listing of sheet names to find out which sheet had the maximum value. Hopefully you should be able to do something like this with your file.

Regards,
Maria

8. ## The Following User Says Thank You to simmo7 For This Useful Post:

hajankel (2011-10-30)

9. Here is crack at a 3d UDF for looking up the sheet.

I started with some code from http://www.j-walk.com/ss/excel/eee/eee003.txt to parse the 3D range (VBA can not do it directly):
Code:
```Function Parse3DRange(sBook As String, SheetsAndRange _
As String, FirstSheet As Integer, LastSheet As Integer, _
sRange As String) As Boolean  Dim sTemp As String
Dim i As Integer
Dim Sheet1 As String
Dim Sheet2 As String
Parse3DRange = False
On Error GoTo Parse3DRangeError
sTemp = SheetsAndRange
i = InStr(sTemp, "!")
If i = 0 Then Exit Function
'next line will generate an error if range is invalid
'if it's OK, it will be converted to absolute form
sRange = Range(Mid\$(sTemp, i + 1)).Address
sTemp = Left\$(sTemp, i - 1)
i = InStr(sTemp, ":")
Sheet2 = Trim(Mid\$(sTemp, i + 1))
If i > 0 Then
Sheet1 = Trim(Left\$(sTemp, i - 1))
Else
Sheet1 = Sheet2
End If
'next lines will generate errors if sheet names are invalid
With Workbooks(sBook)
FirstSheet = .Worksheets(Sheet1).Index
LastSheet = .Worksheets(Sheet2).Index
'swap if out of order
If FirstSheet > LastSheet Then
i = FirstSheet
FirstSheet = LastSheet
LastSheet = i
End If
i = .Worksheets.Count
If FirstSheet >= 1 And LastSheet <= i Then
Parse3DRange = True
End If
End With
Parse3DRangeError:
On Error GoTo 0
Exit Function
End Function  'Parse3DRange```

I then adapted some of the example code for 3d UDFs on that site to create this lookup function:

Code:
```Function FindSheet3D(Range3D As String, Value As Variant)
Dim sTestRange As String
Dim sSumRange As String
Dim Sheet1 As Integer
Dim Sheet2 As Integer
Dim i As Integer

Application.Volatile
If Parse3DRange(Application.Caller.Parent.Parent.Name, _
Range3D, Sheet1, Sheet2, sTestRange) = False Then
FindSheet3D = CVErr(xlErrRef)
End If
For i = Sheet1 To Sheet2
With Worksheets(i)
If .Range(sTestRange) = Value Then
FindSheet3D = .Name
Exit Function
End If
End With
Next
FindSheet3D = CVErr(xlErrRef)
End Function```
If A1 a 3d formula like:
=MAX(start:end!D3)

to calculate the max from cell D3 in a sheet named start to a sheet named end (change as appropriate), then you can use a formula like:
=findsheet3d("start:end!D3",A1)

to find the first sheet in the range containing that value (Note that the 3d reference in this formula is a STRING not a range. the string is parsed to determine the starting and ending sheet and the range with the Parse3d routine).

Steve

10. ## The Following User Says Thank You to sdckapr For This Useful Post:

hajankel (2011-10-30)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•