Results 1 to 3 of 3
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Getting Workbook Names from a consolidation (Excel)

    When data has been consolidated without links, the workbook names are NOT brought in.
    Unless this is done manually, there appears to be no way to get them automatically put into the consolidation.
    Is there any way of doing this.

    Andrew
    Andrew

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Workbook Names from a consolidation (Excel)

    Andrew,

    Are you bringing the consolidated information in by code? If so, is it into one sheet of the workbook or many sheets? If you could provide a little more information that would be great.

    Regards,
    John

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Getting Workbook Names from a consolidation (E

    The data is NOT being generated by macro, but by hand.

    Data from several sheets within the same workbook
    Is being consolidated onto another sheet within the same book.

    The method is linking rather than just a straight consolidate
    (although that also could pose an issue as to where the data comes from)

    When data using left column is consolidated from the same book, the name of the book
    is placed against every data set, not the name of the sheet.

    The data itself contains the name of the relevant sheet as part of the formula,
    BUT, that information is not supplied into an adjacent cell by the consolidation.

    OK. Consolidation is an old tool, but is there any way to get the sheet name into the consolidation results?

    I wrote a function to apply to the name cells, that reads the sheet name from the adjacent linked data cell.

    This works fine, if the data is linked and on the same book.

    If it came from other books, then a code tweak would probably resolve the name issue.
    If the data is NOT linked, then there appears no way to do this.

    Unless someone else knows otherwise.

    Code I use to bodge round an answer is below.

    This can be stored in the same book.
    A personal macro workbook
    Or even an add-in file.

    Function GetSheetName(rng As Range) As String

    Dim strFormula As String, strSheetName As String
    Dim intP As Integer ' used to find ! character

    strFormula = rng.Formula
    intP = InStr(strFormula, "!")

    If intP <> 0 Then
    strSheetName = Left(strFormula, intP - 1)
    If Right(strSheetName, 1) = "'" Then 'Sheet name has spaces
    'Ignore first 2 chars =' and last ' before the !
    strSheetName = Mid(strSheetName, 3, Len(strSheetName) - 3)
    Else
    'Just ignore the first =
    strSheetName = Mid(strSheetName, 2, Len(strSheetName) - 1)
    End If
    Else
    strSheetName = ""
    End If

    GetSheetName = strSheetName

    End Function


    Andrew
    Andrew

Posting Permissions

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