Formula to pull formula from another cell

[youngtrand]

I need a formula to pull a formula from another cell. So for instance, in A1, the formula is "=A2+A3+A4" and I need a formula that I can put in B1 that would reference A1 and return the value of "=B2+B3+B4". Finding a formula is the key here as I would rather not run a macro to copy and paste it. Seems like there should be a function called "copyandpaste" but I guess i've got my head in the clouds. The final goal would be to be able to have one workbook reference another workbook's A1 and have it calculate "=A2+A3+A4" using the A2,A3, and A4 in the original workbook. Thanks for the help!

[mperrah]

if you are looking to have the same formula copy down the same sheet,
hover the mouse over the bottom right corner of the cell with formula and "right" click and drag down , then when you let go you get a prompt choose Copy cells, or Fill series. If you want the cells referenced to remain constant, before you copy, in the orginal formula add a $ in front of the values you dont want to change as you copy the formula.
Like: =$A2+$A3 (keeps "A" constant)
or: =A$2+A$3 (keeps the 2 and 3 constant.
and: =$A$2 + $A$3 (keeps A's and 2 and 3 constant)

Also you may try indirect. look it up in help or google.
it can use the value of a cell as part of another formula.
Hope this helps.
mp

[youngtrand]

if you are looking to have the same formula copy down the same sheet,
hover the mouse over the bottom right corner of the cell with formula and "right" click and drag down , then when you let go you get a prompt choose Copy cells, or Fill series. If you want the cells referenced to remain constant, before you copy, in the orginal formula add a $ in front of the values you dont want to change as you copy the formula.
Like: =$A2+$A3 (keeps "A" constant)
or: =A$2+A$3 (keeps the 2 and 3 constant.
and: =$A$2 + $A$3 (keeps A's and 2 and 3 constant)

Also you may try indirect. look it up in help or google.
it can use the value of a cell as part of another formula.
Hope this helps.
mp


Sorry, but that does not help at all, I will be changing the referenced cell frequently and need the formula to pull whatever is in the cell at that moment, thus the very clear request for a FORMULA

[Aussiebear]

thus the very clear request for a FORMULA

Really? There are a number of conflicting issues in both of your posts that you need to address.


I need a formula to pull a formula from another cell
Are you looking to find the value of the formula from another cell or copy the formula, or are you looking to copy the style of the formula?


I will be changing the referenced cell frequently
Are you simply changing the content of the referenced cell or changing the location of the referenced cell.

[Simon Lloyd]

Why aren't you using SUM? The formula you're looking for (in B1) is =SUM(A1,B2:B4)

[youngtrand]

I reread my post and it came off a little snappy, I was just a little shocked that someone was suggesting copying and pasting as a solution, but it really was my fault, i think I needed to be more specific, I would like to be able to change the formula in Sheet1!A1 from (=sheet1!A2+sheet1!A3+sheet1!A4) to (=sheet1!A2+sheet1!A3+sheet1!A4+sheet1!A5) and have the cell in another sheet i.e. sheet2!D1 adjust from (=sheet2!D2+sheet2!D3+sheet2!D4) to (=sheet2!D2+sheet2!D3+sheet2!D4+sheet2!D5). Clearly I could copy and paste the cell and it would work but I frequently change the formulas in Sheet1 and the values/formulas in sheet2!D2, sheet2!D3, sheet2!D4, sheet2!D5. Sorry for not being more clear the first time around. So basically i would be looking for something like "=essentiallycopyandpaste(Sheet1!A1,Sheet2!D1)"
ya'll are a lot smarter than me, so hopefully there is something like this or some combination of formulas that i'm just not aware of

[Bob Phillips]

This will get the formula from another cell, but I can't really see how you can achieve what you want, even with this

Define three names like so

GETRC: refersto =SUBSTITUTE(REFTEXT(!$A$1),1,"")
cellRef: refersto =MID(GET.CELL(6,INDIRECT(GETRC,FALSE)),FIND("ROW(",
GET.CELL(6,INDIRECT(GETRC,FALSE)))+4,FIND(")",
GET.CELL(6,INDIRECT(GETRC,FALSE)))-FIND("ROW(",
GET.CELL(6,INDIRECT(GETRC,FALSE)))-4)
cellFormula: refersto =GET.FORMULA(INDIRECT(cellRef))&TEXT(NOW(),"")

and then you get the formula like so

=IF(ROW(A1),cellFormula)

[Bob Phillips]

IF you want to 'simplify' it, you could just use the one name, say cellFormula with a refersto value of

=GET.FORMULA(INDIRECT(MID(
GET.CELL(6,INDIRECT(SUBSTITUTE(REFTEXT(!$A$1),1,""),FALSE)),FIND("ROW(",
GET.CELL(6,INDIRECT(SUBSTITUTE(REFTEXT(!$A$1),1,""),FALSE)))+4,FIND(")",
GET.CELL(6,INDIRECT(SUBSTITUTE(REFTEXT(!$A$1),1,""),FALSE)))-FIND("ROW(",
GET.CELL(6,INDIRECT(SUBSTITUTE(REFTEXT(!$A$1),1,""),FALSE)))-4)))&TEXT(NOW(),"")

[youngtrand]

I think this may be a great step towards the finish line, but if i understand this correctly when using the MID formula, it would not work for varying sized formulas...i.e....the formulas i was hoping to have copied are all different, some are SUM, some are AVERAGE, some are two cells multiplied by each other, BUT this has gotten the old gear turning, is there a way you can simply display the formula from another cell in text format and then use another formula to turn that text back into an equation. So for instance A1 would be something like
=ChangeFormulaToText(B1)
and A2 would be
=ChangeTextIntoFormula(A1)
If that is possible then that would solve all my problems

[youngtrand]

Ok, i have figured out how to get the formula into text format, so that's half the battle, I know only need a way to get the text back to a formula, in case you wanted to see what i did to get to text, here is the macro coding
Function FTEXT(f As Range)
If f.HasFormula Then
FTEXT = f.Formula
Else: FTEXT = f
End If
End Function

[Bob Phillips]

When you said formula, I assumed you meant no VBA.

What is the point of your function? My post gave you a way to return the formula in a cell as a text value, regardless of the size. Your function just does what my names and formula do.

The reverse won't work, it already has a formula, and a function cannot change the cell.

[youngtrand]

sorry for being misleading, I thought it would be easiest to just find a formula so that's what i asked for, is there a way to turn the text into a formula using a macro or maybe something like
=IF(A1=0,0,"A1")
the objective being to have the formula be evaluated in that cell, feel free to use a macro, but the idea is to not have to run anything or click anything, just have it update when opened.

[Simon Lloyd]

At this stage it's probably best to produce a sample workbook for us to mess around with and to see your data structure :)

[youngtrand]

I figured it out! Again, sorry about the confusion about no macros Xld, but you really steered me in the right direction, so I owe it all to you anyway, I created two macros

Sub Text()
Function FTEXT(f As Range)
If f.HasFormula Then
FTEXT = f.Formula
Else: FTEXT = f
End If
End Function

Option Explicit
Function Eval(myStr As String) As Variant
Application.Volatile True
Eval = Application.Caller.Parent.Evaluate(myStr)
End Function

The first one grabs the formula from a cell and pastes it as text, the second one grabs text and evaluates it as a formula, so in the cell you just type

=eval(FTEXT(Sheet2!A1))

and it will evaluate the formula in sheet2!A1 using all the cells in your current sheet instead of the ones in sheet2. I have looked through so many threads of people with this problem, I wish I could scream it off the top of a mountain, but all i have is this forum, but again, thank you to Xld for the guidance

[youngtrand]

wait, so actually after applying the formula to my entire sheet, i had to add an argument to the sheet, it is now

=IF([model.xls]Sheet1!A1<1E+27, eval(FTEXT([model.xls]Sheet1!A1)),[model.xls]Sheet1!A1)

in order to allow for words/titles to not return #NAME, the only thing not working now is if any of the cells in model.xls are referencing another workbook. Any ideas on how to allow these to evaluate? The formula will come through fine if I only put

=FTEXT([model.xls]Sheet1!A1

but when I add the =eval() around it, it returns #REF!, sorry about the pump fake on thinking I was done

[youngtrand]

*************UPDATE****************
the error is arriving whenever you try to evaluate a formula that has a directory in the name i.e

=eval(FTEXT(C://Folder/subfolder/[model.xls]sheet1A1))

so when you have the file open, everything works, but if you close the other file, then it won't work anymore (#REF! in every square), i would upload the files, but because there are two i'm not sure if it would be helpful because all the links would be broken

[Simon Lloyd]

Are you referencing the folder properly? wouldn't it be
=eval(FTEXT('C:\Folder\subfolder\model.xls'sheet1!A1))

[youngtrand]

sorry for the delay, yes the reference is correct as it works when the file is open, but when you close the file it gets the error, I could hypothetically copy the tab and move it into the other workbook and link to the copied tab, but that would be a little more hassle than I want because I would still have to copy and paste the tab every time i change an equation

[AirCooledNut]

young, look carefully at the syntax of yours vs. Simon's:
=eval(FTEXT(C://Folder/subfolder/[model.xls]sheet1A1))
=eval(FTEXT('C:\Folder\subfolder\model.xls'sheet1!A1))
The single quotes, the bang (!) are necessary.