Sum of calculation AND worksheet name that it appears on.

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Thanks for the reply 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

Reply | Quote

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

Reply | Quote

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…:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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…:cheers:

Thanks very much indeed for your help..

regards

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote