Results 1 to 10 of 10

Thread: Extracting the sheet name only (as a string) from a formula to another cell

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    1
    Rep Power
    0

    Extracting the sheet name only (as a string) from a formula to another cell

    Hi there

    I am trying to extract the name of the sheet that is referenced in another cell as a string.

    e.g. the formula in A1 is ='Valuation tables'!A52

    I would like A2 to display 'Valuation tables'

    Is that possible? I have been searching for a while now, unsuccessfully. I guess there is a trick using INDIRECT and CELL but I can't work it out.

    Thank you for your help!

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-20-2023 at 03:58 PM.

  2. #2
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    Hi,

    ALT+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code below in on the right. Close VB editor.

    Back on the worksheet you call it like this

    =GetText(A1)

    Code:
    Function GetText(frm As Range) As String
    GetText = Replace(Replace(Left(frm.Formula, _
    InStr(1, frm.Formula, "!", vbTextCompare) - 1), "'", ""), "=", "")
    End Function

  3. #3
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0

    Doesn't work

    Hi Mike

    Thank you very much for your help.. but unfortunately the code doesn't work. It says Compile error: missing library. What should I do?

  4. #4
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Actually: compile error: can't find project or Library. And it highlights the word Left

  5. #5
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    Hi,

    In VB editor click on Tools | References and have a look if any are marked as 'Missing'. If there are then clear the checkmark next to it. The only references you need checked are 'Visual Basic for applications' and 'Microsoft excel nn object library'. Then try again.

  6. #6
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Thanks Mike, you're a genius. Really saved me a lot of time. I couldn't find how to do that anywhere else.. I needed it for a vlookup function according to the tab name in another cell. Works like a charm now! Thank you

  7. #7
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    May I just ask you how to get the row number from the source of a cell? I tried with the MID function.. but unfortunately some tabs different formats (with/without quotes), so it ends up not working.
    Using the same function, how can I get the row #?

    Quote Originally Posted by Mike H View Post
    Hi,

    In VB editor click on Tools | References and have a look if any are marked as 'Missing'. If there are then clear the checkmark next to it. The only references you need checked are 'Visual Basic for applications' and 'Microsoft excel nn object library'. Then try again.

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    or
    Code:
    Function GetText(frm As Range) As String
        GetText = Split(Replace(Mid(frm.Formula, 2), "'", ""), "!")(0)
    End Function
    Code:
    Function GetRow(frm As Range) As Long
        GetRow = frm.row
    End Function
    Last edited by snb; 08-20-2013 at 04:24 PM.

  9. #9
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    Hi,

    I'm not sure I understand the question. How are you addressing the cell you want to get the row number of? You can use expressions like

    Code:
    r=activecell.row
    or you could add this line to the function I gave you and it would return the row number the formula is in.

    Code:
    r = Parent.Caller.Row

  10. #10
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Thank you !

Similar Threads

  1. Highlighting All the Cells of Active sheet which contains a particular String:
    By littleiitin in forum Excel and VBA Tips and Tricks
    Replies: 6
    Last Post: 10-18-2013, 04:19 PM
  2. Replies: 14
    Last Post: 05-25-2013, 06:55 AM
  3. Replies: 1
    Last Post: 02-10-2013, 06:21 PM
  4. Replies: 2
    Last Post: 12-26-2012, 08:31 AM
  5. Find Color in the string using formula.
    By LalitPandey87 in forum Excel Help
    Replies: 4
    Last Post: 07-10-2012, 09:16 PM

Tags for this Thread

Posting Permissions

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