FORMULATEXT function issue

[DPChristman]

I am trying to verify that my separate spreadsheets are all using a consistent formula. However, there are thosands of formulas, an just copying them to each sheet is not giving me the necessary results.
I am trying to use the FORUMLATEXT function.
Specifically, =formulatext(H18)
However, it keeps returning the value of #NAME?
What am i Missing?

 

[Weazel]

does it? I haven't used it in a while so I wasn't sure

well, now that I tested it I guess its not going to work for the OP

 

[DPChristman]

@Weazel

Good thinking, but it does indeed require saving as a macro enabled workook.

I'm afraid there's no way to do it wihtout some form of macro.

Maybe the UDF from the link shared earlier could be saved as an Addin, and then install that addin on your book.
Techincally that's still a macro, but you can use it in a non macro enabled workbook.

Unfortunately, you are now well above my skill level

 

[Jonmo1]

It's really not that difficult to create an addin.
The problem would be that this addin would have to be distributed to and installed by all who you share your workbook with.

The UDF I mentioned was form the link shared in one of the first responses.

Function FT(mycell As Range)
FT = mycell.Formula
End Function

To make that an addin, create a brand new Excel Book
Open the VBA editor and click Insert - New Module
Paste that code there.

Save the XL file as an addin, File - SaveAs - Excel Add-in (*.xlam)
Close the file.

Now open any other Excel file, and go to File - Options - Addins
At the bottom, click Go next to Manage Addins
Click Browse and find the file you saved.

Done.

Now you can use that function like this

=FT(A1)

 

[cerfani]

if you make a code module for this simple function and export for users to download and import, then just require all users to import to their personal workbook. You avoid creating a macro enabled workbook or an addin... everyone should have a personal file and be able to import functions

functions on the personal workbook simply need reference to the file name...=Personal.xlsb!TheFunction()

 

[63falcondude]

You can hit Ctrl ` to show all formulas. The symbol is with ~. Maybe this will help you.