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

[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]

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.

[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