LinkBack URL
About LinkBacks
Like the title says, I am wondering if there is an equivalent function to FORMULATEXT in ME 2007. I haven't been able to find one.
Like the title says, I am wondering if there is an equivalent function to FORMULATEXT in ME 2007. I haven't been able to find one.
Who needs a life when you have Excel.
If I am understanding, you wish to have the formula appear as text in the cell. To do this, click on Formulas Tab and then on the Show formulas. If this is not what you are looking for, then please explain further as we are Excel experts but not mind readers.
Alan
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
What the formula text function does is actually treats the referenced cell formula as a text string. What this does is allow you to use such functions as substitute and replace (or any text function) on those before mentioned formulas, and make changes to them without having to manually go in. This function is also useful where you have several helper cells that you want to string together for compactness in the sheet (with the help of concatenate or &). It is a great function to use, but it is not available in the version of excel I have, and I was asking what equivalent function/process is available (for my version of excel) that converts referenced cell formulas into text strings. Aside from copy&paste.
There is no built-in function equivalent in Excel versions 2010 and earlier.
There is an old XL4 macro function that will do it but you may as well go all the way with a VBA user defined function.
Are you familiar with using macros?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I am familiar with macros, but I am definitely limited to the basics in that realm of excel. I know how to record/assign them, and can stumble my way through basic manual coding if the recorded code is not too complicated. But beyond that, no.
@TheMechEngineer Thanks for enlightening me. Was not aware of this function as I have not previously needed it.
Copy the code below and paste it into a general module:
Then, to use it as a worksheet function...
Please Login or Register to view this content.
=GetFormula(cell address)
=GetFormula(A1)
You'll have to save the file as a macro enabled file in the *.xlsm file format.
It was introduced in Excel 2013.Originally Posted by alansidman
Thanks Tony,
Just tried it, and it worked.
I have been wanting to get into the Visual Basic aspect of Excel for reasons just like this.
You're welcome. Thanks for the feedback!
Almost 3 years later and this fix just saved me. (Yes some people are still stuck in 2010)
Ha! In Northern Ireland, we're still stuck in 1690...
Glenn
Nearly another year on and it is STILL getting attention. Thanks! In my case, I found this, created a UDF and then found I already had one. Apparently I'd needed to include getting the text of Array Formulae so here's the adaptation in case there are any other deadheads needing zombie threads for our old, but good, licensed programs!
Why "Public"? Because I store the functions in Personal.xlsm so that they are available to other workbooks as shown in the commented example.Please Login or Register to view this content.
Hope that helps someone who got in the same bind that I was in.
As suggested above XL 4.0 macros are sufficient if someone does not wish to go for a UDF:
All you need to do is define a name of: =GET.CELL(6,Sheet1!G12)
I was in H12 when I defined the name. It is important to remove any $ in the range reference thereby creating a relative reference.
The name given here was TEXTFORMULA
This way, typing =textformula will always give the text value of any cell's formula to its immediate left
Yes I tried that solution when I first came across this problem and used other XL 4.0 macros trying to solve other problems. However they rendered workbooks simply unworkable including crashing the application. Hence the solution to use a very simple UDF. I am only replying to ensure that those less versed in Excel functions and/or VBA don't head down what is a risky path using XL 4.0 with all the compatibility problems.
Yup, I've read about some crashing problems involving 4.0 macros.
I myself cannot report any such case over the last 15 years though.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks