|
Displaying Cell Formulas (without using Tools/Options/View-Formula
Is there any way to display the Formula of another individual cell (not all
cells on the Worksheet as per using Tools / Options / View then selecting Formulas Check box) using Standard Excel functions (not macros). ie if Cell A1 has formula =A2+1 want to create a method of displaying the formula (as text) in another Cell Trying to avoid Macros but could do this easily by creating a VB Function like Public Function TextFormula(rngFormula As Range) As String TextFormula = rngFormula.Formula End Function Then having in the other Cell =TextFormula(A1) |
Displaying Cell Formulas (without using Tools/Options/View-Formula
Hi,
you can try this. Go to insert Name Define and assign a name there - "showformula". In the refers to box, type =GET.CELL(6,INDIRECT("RC[-1]",)). In cell B1, enter =showformula. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DSMessenger" wrote in message ... Is there any way to display the Formula of another individual cell (not all cells on the Worksheet as per using Tools / Options / View then selecting Formulas Check box) using Standard Excel functions (not macros). ie if Cell A1 has formula =A2+1 want to create a method of displaying the formula (as text) in another Cell Trying to avoid Macros but could do this easily by creating a VB Function like Public Function TextFormula(rngFormula As Range) As String TextFormula = rngFormula.Formula End Function Then having in the other Cell =TextFormula(A1) |
Displaying Cell Formulas (without using Tools/Options/View-For
Thanks constantly amazed by the depth of Excel knowledge of people on this
forum. That works great but I would like to understand how it is done. Understand the use of INDIRECT Function and assume using R1C1 Style reference, rather than A1 Style reference, so that the Name works across multiple Worksheets without having a Sheet Reference. Understand the use of CELL Function (and previously tried to use it to do this) but only knewi nfotypes address, col, color, contents, filename, Format, parenthesis, prefix, protect, row, type, and "width". So not sure whether 6 is one of these or something different altogether. Searched the Excel Help File for GET and dont find it. Although if I search for GET in VB Help File it finds lots of Get*** type keywords and if I type GET into a VB module it obviously understands it. Any suggestions where to look so I can get more information ? "Ashish Mathur" wrote: Hi, you can try this. Go to insert Name Define and assign a name there - "showformula". In the refers to box, type =GET.CELL(6,INDIRECT("RC[-1]",)). In cell B1, enter =showformula. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DSMessenger" wrote in message ... Is there any way to display the Formula of another individual cell (not all cells on the Worksheet as per using Tools / Options / View then selecting Formulas Check box) using Standard Excel functions (not macros). ie if Cell A1 has formula =A2+1 want to create a method of displaying the formula (as text) in another Cell Trying to avoid Macros but could do this easily by creating a VB Function like Public Function TextFormula(rngFormula As Range) As String TextFormula = rngFormula.Formula End Function Then having in the other Cell =TextFormula(A1) |
Displaying Cell Formulas (without using Tools/Options/View-For
Also I visited your Website Ashish, and added it to my list of Excel Sites,
and will take the time to read through some of your other examples. I am in Australia so a long way away from India. Thanks again. "Ashish Mathur" wrote: Hi, you can try this. Go to insert Name Define and assign a name there - "showformula". In the refers to box, type =GET.CELL(6,INDIRECT("RC[-1]",)). In cell B1, enter =showformula. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DSMessenger" wrote in message ... Is there any way to display the Formula of another individual cell (not all cells on the Worksheet as per using Tools / Options / View then selecting Formulas Check box) using Standard Excel functions (not macros). ie if Cell A1 has formula =A2+1 want to create a method of displaying the formula (as text) in another Cell Trying to avoid Macros but could do this easily by creating a VB Function like Public Function TextFormula(rngFormula As Range) As String TextFormula = rngFormula.Formula End Function Then having in the other Cell =TextFormula(A1) |
Displaying Cell Formulas (without using Tools/Options/View-For
After about an hour of searching the Internet I think I have now answered my
"Reply" Question. Can't find GET in the Excel Help File because it isn't there. And it isn't there because it is an old Excel 4 XLM Type Macro. Found all this from Chip Pearsons site http://cpearson.com/excel/Call.htm Therefore went to Microsoft Support site http://support.microsoft.com/kb/128185 and downloaded the Macrofun.hlp Help File which explains GET and the values that can be passed into it to get various outputs. So today has been an interesting day learning lots of new things. "DSMessenger" wrote: Thanks constantly amazed by the depth of Excel knowledge of people on this forum. That works great but I would like to understand how it is done. Understand the use of INDIRECT Function and assume using R1C1 Style reference, rather than A1 Style reference, so that the Name works across multiple Worksheets without having a Sheet Reference. Understand the use of CELL Function (and previously tried to use it to do this) but only knewi nfotypes address, col, color, contents, filename, Format, parenthesis, prefix, protect, row, type, and "width". So not sure whether 6 is one of these or something different altogether. Searched the Excel Help File for GET and dont find it. Although if I search for GET in VB Help File it finds lots of Get*** type keywords and if I type GET into a VB module it obviously understands it. Any suggestions where to look so I can get more information ? "Ashish Mathur" wrote: Hi, you can try this. Go to insert Name Define and assign a name there - "showformula". In the refers to box, type =GET.CELL(6,INDIRECT("RC[-1]",)). In cell B1, enter =showformula. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DSMessenger" wrote in message ... Is there any way to display the Formula of another individual cell (not all cells on the Worksheet as per using Tools / Options / View then selecting Formulas Check box) using Standard Excel functions (not macros). ie if Cell A1 has formula =A2+1 want to create a method of displaying the formula (as text) in another Cell Trying to avoid Macros but could do this easily by creating a VB Function like Public Function TextFormula(rngFormula As Range) As String TextFormula = rngFormula.Formula End Function Then having in the other Cell =TextFormula(A1) |
Displaying Cell Formulas (without using Tools/Options/View-For
You are welcome.
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DSMessenger" wrote in message ... Thanks constantly amazed by the depth of Excel knowledge of people on this forum. That works great but I would like to understand how it is done. Understand the use of INDIRECT Function and assume using R1C1 Style reference, rather than A1 Style reference, so that the Name works across multiple Worksheets without having a Sheet Reference. Understand the use of CELL Function (and previously tried to use it to do this) but only knewi nfotypes address, col, color, contents, filename, Format, parenthesis, prefix, protect, row, type, and "width". So not sure whether 6 is one of these or something different altogether. Searched the Excel Help File for GET and dont find it. Although if I search for GET in VB Help File it finds lots of Get*** type keywords and if I type GET into a VB module it obviously understands it. Any suggestions where to look so I can get more information ? "Ashish Mathur" wrote: Hi, you can try this. Go to insert Name Define and assign a name there - "showformula". In the refers to box, type =GET.CELL(6,INDIRECT("RC[-1]",)). In cell B1, enter =showformula. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DSMessenger" wrote in message ... Is there any way to display the Formula of another individual cell (not all cells on the Worksheet as per using Tools / Options / View then selecting Formulas Check box) using Standard Excel functions (not macros). ie if Cell A1 has formula =A2+1 want to create a method of displaying the formula (as text) in another Cell Trying to avoid Macros but could do this easily by creating a VB Function like Public Function TextFormula(rngFormula As Range) As String TextFormula = rngFormula.Formula End Function Then having in the other Cell =TextFormula(A1) |
Displaying Cell Formulas (without using Tools/Options/View-For
Thank you.
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DSMessenger" wrote in message ... Also I visited your Website Ashish, and added it to my list of Excel Sites, and will take the time to read through some of your other examples. I am in Australia so a long way away from India. Thanks again. "Ashish Mathur" wrote: Hi, you can try this. Go to insert Name Define and assign a name there - "showformula". In the refers to box, type =GET.CELL(6,INDIRECT("RC[-1]",)). In cell B1, enter =showformula. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DSMessenger" wrote in message ... Is there any way to display the Formula of another individual cell (not all cells on the Worksheet as per using Tools / Options / View then selecting Formulas Check box) using Standard Excel functions (not macros). ie if Cell A1 has formula =A2+1 want to create a method of displaying the formula (as text) in another Cell Trying to avoid Macros but could do this easily by creating a VB Function like Public Function TextFormula(rngFormula As Range) As String TextFormula = rngFormula.Formula End Function Then having in the other Cell =TextFormula(A1) |
| All times are GMT +1. The time now is 11:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com