• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Manipulate cell formula without using VBA

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
johnfaig
Asked:
johnfaig
1 Solution
 
CluskittCommented:
=GetFormula(A1)
0
 
Rory ArchibaldCommented:
Only by using GET.CELL in a defined name and that is also technically a macro. Why can't you use VBA?
0
 
Rory ArchibaldCommented:
GetFormula does not exist - it would be a UDF created in VBA.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
CluskittCommented:
Sorry, getformula is my macro. I forgot that excel doesn't have it. :P
0
 
jeffw1Commented:
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
 
Rory ArchibaldCommented:
Per the OP: " I don't want to use VBA.  "


0
 
CluskittCommented:
I don't think you can manipulate the formula in another cell using pure excel. Some form of vba is always required.
0
 
Rory ArchibaldCommented:
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
 
CluskittCommented:
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
 
MWGainesJRCommented:
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
 
Rory ArchibaldCommented:
Yes you can - it is returned as a string.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now