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

[aperrin8]

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/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.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Mike H]

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)



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

[aperrin6]

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?

[aperrin6]

Actually: compile error: can't find project or Library. And it highlights the word Left

[Mike H]

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.

[aperrin6]

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

[aperrin6]

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 #?


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.

[snb]

or

Function GetText(frm As Range) As String
GetText = Split(Replace(Mid(frm.Formula, 2), "'", ""), "!")(0)
End Function


Function GetRow(frm As Range) As Long
GetRow = frm.row
End Function

[Mike H]

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



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.



r = Parent.Caller.Row

[aperrin6]

Thank you !