Solved

Manipulate cell formula without using VBA

Posted on 2010-08-19
11
338 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
ID: 33474263
=GetFormula(A1)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33474266
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
ID: 33474290
GetFormula does not exist - it would be a UDF created in VBA.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

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

Expert Comment

by:jeffw1
ID: 33474360
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
 
LVL 85

Expert Comment

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


0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33474451
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
ID: 33474535
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
ID: 33474557
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
ID: 33474595
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
ID: 33474600
Yes you can - it is returned as a string.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

713 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