Tab Name Reference

[Sheri]

I am close, but no cigar. I have a formula in cell A1 that is =TestSheet!A1,
in B1 I have the following formula - =MID(CELL("filename",A1), FIND("]",
CELL("filename", A1))+ 1, 255), which results in it saying Sheet1. What I
need is for the formula in cell B1 to tell me which sheet A1 is coming from.
So ideally in B1, it would say TestSheet. Any suggestions?

 

[Bernard Liengme]

Here is a UDF that does that

Function getname(mycell)
myform = mycell.Formula
x = InStr(1, myform, "!")
getname = Mid(myform, 2, x - 2)
End Function


David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

best wishes

 

[Sheri]

Bernard, thank you. I am not that savvy with UDFs, how do I get it to work?

Here is a UDF that does that

Function getname(mycell)
myform = mycell.Formula
x = InStr(1, myform, "!")
getname = Mid(myform, 2, x - 2)
End Function


David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am close, but no cigar. I have a formula in cell A1 that is
=TestSheet!A1,
in B1 I have the following formula - =MID(CELL("filename",A1), FIND("]",
CELL("filename", A1))+ 1, 255), which results in it saying Sheet1. What I
need is for the formula in cell B1 to tell me which sheet A1 is coming
from.
So ideally in B1, it would say TestSheet. Any suggestions?

 

[Bernard Liengme]

Follow the links in my message
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Bernard, thank you. I am not that savvy with UDFs, how do I get it to
work?

Here is a UDF that does that

Function getname(mycell)
myform = mycell.Formula
x = InStr(1, myform, "!")
getname = Mid(myform, 2, x - 2)
End Function


David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am close, but no cigar. I have a formula in cell A1 that is
=TestSheet!A1,
in B1 I have the following formula - =MID(CELL("filename",A1),
FIND("]",
CELL("filename", A1))+ 1, 255), which results in it saying Sheet1.
What I
need is for the formula in cell B1 to tell me which sheet A1 is coming
from.
So ideally in B1, it would say TestSheet. Any suggestions?

 

[Sheri]

Bernard, thank you, it is working beautifully!

Follow the links in my message
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Bernard, thank you. I am not that savvy with UDFs, how do I get it to
work?

Here is a UDF that does that

Function getname(mycell)
myform = mycell.Formula
x = InStr(1, myform, "!")
getname = Mid(myform, 2, x - 2)
End Function


David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am close, but no cigar. I have a formula in cell A1 that is
=TestSheet!A1,
in B1 I have the following formula - =MID(CELL("filename",A1),
FIND("]",
CELL("filename", A1))+ 1, 255), which results in it saying Sheet1.
What I
need is for the formula in cell B1 to tell me which sheet A1 is coming
from.
So ideally in B1, it would say TestSheet. Any suggestions?

 

[Bernard Liengme]

Well done!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Bernard, thank you, it is working beautifully!

Follow the links in my message
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Bernard, thank you. I am not that savvy with UDFs, how do I get it to
work?

:

Here is a UDF that does that

Function getname(mycell)
myform = mycell.Formula
x = InStr(1, myform, "!")
getname = Mid(myform, 2, x - 2)
End Function


David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am close, but no cigar. I have a formula in cell A1 that is
=TestSheet!A1,
in B1 I have the following formula - =MID(CELL("filename",A1),
FIND("]",
CELL("filename", A1))+ 1, 255), which results in it saying Sheet1.
What I
need is for the formula in cell B1 to tell me which sheet A1 is
coming
from.
So ideally in B1, it would say TestSheet. Any suggestions?