Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Manipulate cell formula without using VBA

Posted on 2010-08-19
11
Medium Priority
?
349 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: 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 2000 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

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!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

721 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