function to give me cell reference
Is there a function that returns the formula in another cell in text?
For example, if cell A1 has the formula: =Sheet2!B2 I want B1 to say in text: Sheet2!B2 |
function to give me cell reference
Hi,
While on cell A1, go to Data Text to columns Delimited and select Other. in the other box, type = and click on next. in the destination cell box, select B1. Click on Finish. please note that this is not a dynamic solution I.e. if the formula changes in cell A1, then the result in cell C1 will not change. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Josh Craig" wrote in message ... Is there a function that returns the formula in another cell in text? For example, if cell A1 has the formula: =Sheet2!B2 I want B1 to say in text: Sheet2!B2 |
function to give me cell reference
You can try Ctrl+~
Does that do it for you? HTH, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Ashish Mathur" wrote: Hi, While on cell A1, go to Data Text to columns Delimited and select Other. in the other box, type = and click on next. in the destination cell box, select B1. Click on Finish. please note that this is not a dynamic solution I.e. if the formula changes in cell A1, then the result in cell C1 will not change. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Josh Craig" wrote in message ... Is there a function that returns the formula in another cell in text? For example, if cell A1 has the formula: =Sheet2!B2 I want B1 to say in text: Sheet2!B2 |
function to give me cell reference
Hi Ashish. Thanks for the answer. So there's definitely no dynamic way to
do it? Would you know if there's a way to get a column in a summary sheet to display the names of all the other sheets? e.g. Col A (in Sheet4): Sheet1 Sheet2 Sheet3 "Ashish Mathur" wrote: Hi, While on cell A1, go to Data Text to columns Delimited and select Other. in the other box, type = and click on next. in the destination cell box, select B1. Click on Finish. please note that this is not a dynamic solution I.e. if the formula changes in cell A1, then the result in cell C1 will not change. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Josh Craig" wrote in message ... Is there a function that returns the formula in another cell in text? For example, if cell A1 has the formula: =Sheet2!B2 I want B1 to say in text: Sheet2!B2 |
function to give me cell reference
For a dynamic way, you could use a user defined function.
Go to the VBA Editor (Alt+F11), in the left Project pane click on your workbook (VBA Project (workbookname)), rightclick and insert a module. Doubleclick the new module (probably named Module1), and into the right text field copy following function: Function CellFormula(c) CellFormula = c.Formula End Function Now you can go back to your workbook and enter into B1 the formula =CellFormula(A1). Cheers Joerg Mochikun "Josh Craig" wrote in message ... Hi Ashish. Thanks for the answer. So there's definitely no dynamic way to do it? Would you know if there's a way to get a column in a summary sheet to display the names of all the other sheets? e.g. Col A (in Sheet4): Sheet1 Sheet2 Sheet3 "Ashish Mathur" wrote: Hi, While on cell A1, go to Data Text to columns Delimited and select Other. in the other box, type = and click on next. in the destination cell box, select B1. Click on Finish. please note that this is not a dynamic solution I.e. if the formula changes in cell A1, then the result in cell C1 will not change. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Josh Craig" wrote in message ... Is there a function that returns the formula in another cell in text? For example, if cell A1 has the formula: =Sheet2!B2 I want B1 to say in text: Sheet2!B2 |
function to give me cell reference
So there's definitely no dynamic way to do it?
Try this UDF (user defined function) Function GetFormula(cell_ref As Range) As String GetFormula = "" If cell_ref.HasFormula Then GetFormula = cell_ref.Formula End If End Function To install it: Open the VBE - ATL F11 Open Project Explorer - CTRL R Locate your file name. It'll look like this: VBAProject (your_file_name) Right click on your file name. Select InsertModule Copy/paste the code above into the window that opens on the right. Close the VBE and return to Excel - ALT Q To use it on your worksheet - use it like any other function: =GetFormula(A1) If the referenced cell does not contain a formula GetFormula returns a blank. -- Biff Microsoft Excel MVP "Josh Craig" wrote in message ... Hi Ashish. Thanks for the answer. So there's definitely no dynamic way to do it? Would you know if there's a way to get a column in a summary sheet to display the names of all the other sheets? e.g. Col A (in Sheet4): Sheet1 Sheet2 Sheet3 "Ashish Mathur" wrote: Hi, While on cell A1, go to Data Text to columns Delimited and select Other. in the other box, type = and click on next. in the destination cell box, select B1. Click on Finish. please note that this is not a dynamic solution I.e. if the formula changes in cell A1, then the result in cell C1 will not change. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Josh Craig" wrote in message ... Is there a function that returns the formula in another cell in text? For example, if cell A1 has the formula: =Sheet2!B2 I want B1 to say in text: Sheet2!B2 |
All times are GMT +1. The time now is 10:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com