| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Name Reference
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tab Name Reference
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" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tab Name Reference
Bernard, thank you. I am not that savvy with UDFs, how do I get it to work?
"Bernard Liengme" wrote: 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" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tab Name Reference
Follow the links in my message
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sheri" wrote in message ... Bernard, thank you. I am not that savvy with UDFs, how do I get it to work? "Bernard Liengme" wrote: 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" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tab Name Reference
Bernard, thank you, it is working beautifully!
"Bernard Liengme" wrote: Follow the links in my message -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sheri" wrote in message ... Bernard, thank you. I am not that savvy with UDFs, how do I get it to work? "Bernard Liengme" wrote: 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" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tab Name Reference
Well done!
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sheri" wrote in message ... Bernard, thank you, it is working beautifully! "Bernard Liengme" wrote: Follow the links in my message -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sheri" wrote in message ... Bernard, thank you. I am not that savvy with UDFs, how do I get it to work? "Bernard Liengme" wrote: 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" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing sheet reference to cell reference | Excel Worksheet Functions | |||
| Column letter reference as number reference | Excel Programming | |||
| Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
| Row reference increment but preserve column reference | Excel Worksheet Functions | |||
| Macro to Reference Column Next to Current Reference | Excel Discussion (Misc queries) | |||
Linear Mode
