Solved

Manipulate cell formula without using VBA

Posted on 2010-08-19
11
329 Views
Last Modified: 2012-05-10
Is it possible to get the formula of a cell instead of the value?  For example, let's say that cell A1 has =4*(10-6)-2.  Can I get the formula into another cell, so I can manipulate it using standard Excel string formulas.  I don't want to use VBA.  
0
Comment
Question by:johnfaig
11 Comments
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
=GetFormula(A1)
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Only by using GET.CELL in a defined name and that is also technically a macro. Why can't you use VBA?
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
GetFormula does not exist - it would be a UDF created in VBA.
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
Sorry, getformula is my macro. I forgot that excel doesn't have it. :P
0
 

Expert Comment

by:jeffw1
Comment Utility
Click in the cell, the formula will show in the formula bar where it can be edited.

If you just want to see the formula in another cell create a function.

Function GetFormula(Cell as Range) as String
   GetFormula = Cell.Formula
End Function

Then use it to show the formula
 
  =GetFormula(A1)  
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Per the OP: " I don't want to use VBA.  "


0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
I don't think you can manipulate the formula in another cell using pure excel. Some form of vba is always required.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
No, you can read it using XLM functions, but you can't enter them directly into the cell, so you have to use them in a defined name and then enter that defined name in the cell instead.
For example, define a name called GetFormula and in the Refersto box enter:
=GET.CELL(6,INDIRECT("RC[1]",0))
Now if you enter =GetFormula in a cell (note: do not use =GetFormula() or Excel will think it's a UDF; you must omit the parentheses) it will return the formula in the cell to its right.
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
That allows for replication, not manipulation, though. You can't, for example, use:
=RIGHT(GetFormula,4)

Or can you? If you can, than I believe that's what the poster wants, except that the range has to be fixed, instead of specified.
0
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
May I ask what your goal is by this?
Let's say you want "=sum(A1:A5)" in A6 of EVERY worksheets......
In excel you can hold shift and select all worksheets, then whatever you type in a cell on the first worksheet, all others will be updated in the same exact way automatically.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Yes you can - it is returned as a string.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now