Tab Name Reference

  • Thread starter Thread starter Sheri
  • Start date Start date
S

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, thank you. I am not that savvy with UDFs, how do I get it to work?

Bernard Liengme said:
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

Sheri said:
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?
Click to expand...
Click to expand...
 
Follow the links in my message
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

Bernard Liengme said:
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

Sheri said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Bernard, thank you, it is working beautifully!

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

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

Bernard Liengme said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Well done!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Sheri said:
Bernard, thank you, it is working beautifully!

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

Sheri said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top