Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Returning sheet name from formula

  1. #1
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mudface,

    Thanks to Jay and Ivan, we have the answer:
    http://www.mrexcel.com/board/viewtop...c=5003&forum=2

    and the formula would be:
    =MID(getformula,2,SEARCH("!",getformula))
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mudface,

    Thanks to Jay and Ivan, we have the answer:
    http://www.mrexcel.com/board/viewtop...c=5003&forum=2

    and the formula would be:
    =MID(getformula,2,SEARCH("!",getformula))
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If a have a formula such as: -

    =Sheet2!A1

    is there a way of returning which sheet is referenced by the formula (ie in a separate cell I would like to have Sheet2 returned)?

    Cheers for any help.

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another option. Why not specify the sheet in another cell and pull it for the formula. So in cell A1 is the value:
    Sheet2
    and in cell A2 is the formula:
    =INDIRECT(A1 & "!B17")



  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Rob, I did think of trying that, but thought it would be easier just to Copy the data from the source file and Paste it as a link (there are four spreadsheets, each with 3 sheets and I wanted around a 1000 rows by 20 columns from each sheet linked so I could summarise the source data via a pivot table). Might create another sheet, using your suggestion and see if that can be done easily (sigh) .

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about using VBA:
    Code:
    Dim formula As String
    Dim ExlLoc As Integer
    formula = ActiveCell.formula
    ExlLoc = Application.WorksheetFunction.Search("!", formula)
    ActiveCell.Offset(0, 1) = Mid(formula, 2, ExlLoc - 2)
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  7. #7
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Couldn't you just use one of Aladin's or Mark W's formulas that use =LEFT(A1...blah blah blah. I think I saw something like this posted in the last day or so.



  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark: You mean

    =LEFT(C20,FIND("!",C20,1)-1)
    and that will give Sheet2

    [ This Message was edited by: Yogi Anand on 2002-04-11 14:13 ]

  9. #9
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi,

    That could very well be something like that to which I was referring.

  10. #10
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I may be wrong here, but I don't think that formula works. Doesn't find only search the value of the cell and not the formula?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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