Getting formula of another cell in target cell

Question

How does one cell obtain the formula of another cell as text without using VBA? I can see this question has already been asked many times and the answer is always to write a custom function in VBA.

However, I found a post made in 2006 which claimed to have found the non-VBA solution but the link provided in that post is already broken.

If you're looking for a non-VBA solution, this isn't really a programming question, is it? It should be a [superuser](http://superuser.com) question instead, I'd think. — Ken White, Feb 03 '12 at 00:41
That missing post seems to have pointed to an approach using the old XLM `GET.FORMULA()` , but if you were willing to go that far you should just use a regular VBA function. — Tim Williams, Feb 03 '12 at 02:02
Not sure what XLM GET.FORMULA() is, but I guess I will stick with the VBA method. — Pupper, Feb 03 '12 at 02:09
@Scott - That is the inverse from what was asked: Getting a formula as text, not getting a text used as formula (plus, it is VBA, exactly what the OP meant to avoid). — sancho.s ReinstateMonicaCellio, Jan 07 '14 at 13:54

score 29 · Answer 1 · edited Aug 12 '16 at 15:22

29

=FormulaText(Reference) will do the trick Documentation

edited Aug 12 '16 at 15:22

Chrismas007

6,085
4
24
47

answered May 09 '14 at 00:39

Excel User

291
3
2

1

Could you provide a link to documentation? – sancho.s ReinstateMonicaCellio Jan 22 '15 at 13:39
1

Don't know when they introduced the FormulaText function, but it seems to work in Excel2013. Just what I need... thanks! – veljkoz Feb 28 '15 at 13:50
10

It does not exist in Excel 2010. – sancho.s ReinstateMonicaCellio Mar 27 '15 at 11:08

sancho.s ReinstateMonicaCellio · Accepted Answer · 2017-10-02T06:15:38.683

There is nice way of doing this without VBA. It uses XL4 macros (these are macros, but it is not VBA, as asked).

With reference to the figure 1, cells A2:A4 contain usual formulas.

enter image description here

Going to Formulas -> Define Name, I defined two named ranges (see fig. 2), with the information shown in cells A6:B8.
Enter in cell B2 =FormulaAsText. This will retrieve the formula in cell A2 as text.

Explanation: The named range FormulaAsText uses =GET.CELL(info_type,reference). In this case, ìnfo_type = 6 retrieves the formula, and reference = OFFSET(INDIRECT("RC",FALSE),0,-1) uses the cell with 0 rows and -1 columns offset from the one the formula is used in.
Copy B2 and paste into B3:B4. This will show formulas in A3:A4. Cell A4 shows that the worksheet function CELL only retrieves values, not formulas (as opposed to GET.CELL).
Since FormulaAsText gets the formula from a cell at fixed offset (0,-1) from the current, I defined another range FormulaAsText2, which uses an offset (rows,cols) read from the worksheet itself. Cells D2:D4 contain =FormulaAsText2. Thus, cell D2 shows the contents of cell B3 (=OffSET(D2,1,-2)), which is FormulaAsText. cells D3:D4 show the contents of themselves. This adds some flexibility. YMMV.

PS1: The essence was taken from http://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html

PS2: Tim Williams mentioned in a comment "the old XLM GET.FORMULA()". This answer is possibly related (not the same, since this one uses GET.CELL()).

PS3: A simple VBA solution is given, e.g., in http://dmcritchie.mvps.org/excel/formula.htm

EDIT: Complementing this nice answer, the worksheet function FormulaText is available for Excel 2013 and later.

score 2 · Answer 3 · answered Jan 27 '17 at 15:42

This suggestion may be helpful for those who after retrieving a block of formulas and transporting them to a new spreadsheet want to put them to work again. Excels FORMULATEXT function is great for picking up formulas but it leaves them as unusable text strings. If you want to get them back as fully functioning formulas you have to edit each one individually to remove the string character, but here is a shortcut for larger blocks. Get to the position where you have the required formulas as text (in other words after using FORMULATEXT - you have done a copy and (value only) paste). The next step involves highlighting all the cells you want to convert and then navigating to the [Text-To-Columns] menu option ({Data} bar on Excel 2016). You can select 'Delimited' but on the next screen just make sure you de-select any marks that do appear in your formulas. Then 'Finish'. Excel should automatically analyse the cells as containing formulas and you should now have them working again.

score 0 · Answer 4 · answered May 31 '16 at 21:15

There is a way to do this. In my example I had a table that showed a date. The date comes from Sheet!G91. In my table I also had a column that showed the sheet name. I added two more columns to my table. The first column had column(Sheet!g91), which returns the number 7, because G is the seventh letter in the alphabet. I then converted the number to a letter (G) using another table in my workbook. In the second column that I added, I made a formula row(Sheet!G91), which returns the number 91. Note: Row and Column may appear as volatile formulas, which recalculate with every calculation of the workbook.

I wanted another column to show the formula contents of the date cell mentioned at the beginning of this post. I included the following string function (you can also use CONCATENATE).

"=" & AJ9 & "!" & AM9 & AN9

The items separated by ampersands get strung together (that is, concatenated). AJ9 in my example contains the sheet name, AM9 contains the column letter, and AN9 contains the row number.

I now have a column that dynamically updates its contents to reflect the sheet name and cell reference. The results in my workbook cell are

=Sheet!G91.

This approach won't work for formulas generally, though, which I think is what the question is asking for. — Matthew Strawbridge, May 31 '16 at 21:37

score -6 · Answer 5 · answered Feb 03 '12 at 01:32

-6

You can't. This is most likely a design choice to eliminate an average Excel user from accidentally getting something they did not want.

What you are reading is correct - writing a UDF is the solution you want.

answered Feb 03 '12 at 01:32

Gaijinhunter

14,587
4
51
57

Thanks Issun (Okami?), I guess VBA is the way to go. – Pupper Feb 03 '12 at 02:10
I think that this answer is not correct, and I posted an alternative. – sancho.s ReinstateMonicaCellio Jan 07 '14 at 13:48
Using XL4 is hardly what I would call being able to get it using normal excel formulas (without VBA). That would be like saying "well you can get the formula using C# so there, it's not VBA! The point of the question is more about how to do it using normal Excel functionality (minus VBA) not how to do it *not* using VBA. – Gaijinhunter Jan 09 '14 at 06:28
@Issun - Your interpretation might or might not be the intention of the OP, I wouldn't vouch for any of the two options. The question did not ask for "using normal excel formulas" (perhaps it was the intention, or perhaps S200 meant to avoid *only* VBA). I am only saying that the answer to the question posted is "You can..." By the same token, a solution with C# *is* non-VBA, so I would personally consider it another example backing "You can". You would have probably formulated accurately the question you answered, and that is of course valid, but not this case. – sancho.s ReinstateMonicaCellio Mar 12 '14 at 02:25
I am not interested in debating this. The OP had explicitly put the "formulas" tag on the question to make it clear what he/she was asking. There are many ways 'you can' like printing it out and looking at it, using apple script, using C#, etc. but that's stupid to even debate. – Gaijinhunter Mar 12 '14 at 04:16
@Issun - The tag "formulas" possibly alludes to the fact that the OP wanted to retrieve the formula in a cell (which is what he/she was clearly asking), and not because he/she wanted to do that with another formula. Possibly not worth a debate (and this is likely not a debate anyway). But it is good to exchange ideas. – sancho.s ReinstateMonicaCellio Mar 15 '14 at 01:53

Getting formula of another cell in target cell

5 Answers5

Linked

Related