Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #3
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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...
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    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. #7
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    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
    Attached Files Attached Files
    Maria
    Simmo7
    Victoria, Australia

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

    hajankel (2011-10-30)

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
  •