• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to reference formula

P

patsfan

Member
Is there a formula to reference the formula of another cell?

Or a way to insert a string into a filename reference?


In A1, I have account number "1234".

In B1, I reference the range named "AcctTotal" from another file. "=+'c:testprojectslistsAccount 1234 Total.xlsx'!AcctTotal".

I'm looking for a check to ensure the filename referenced in B1 refers to the account number in A1.

Option 1 is the ability to include the contents of cell A1 into the reference string in B1.

Option 2 is to extract the formula in B1 and use the MID function to compare the number in the filename against the account number in A1.

I'm not sure which would be easier to use.

Any advice would be appreciated.

Thanks
 
Before getting too far, both options would require the use of VB.


Option 1

Ideally, it would be nice if we could use the INDIRECT function to accomplish this. However, INDIRECT won't work on closed external workbooks, so this becomes un-usable. We could write some VB script that would update links based on a value in cell A1.


Option 2

Within native XL, there's no way to get the formula within a cell, only the resultant value. However, you could use this short UDF to accomplish that goal

[pre]
Code: 
Function GetFormula(r As Range, Optional x As Boolean = False) As String
If r.Count > 1 Then GetFormula = "#VALUE!"
'Check if formula returned in R1C1 style
If x Then
GetFormula = r.FormulaR1C1
Else
GetFormula = r.Formula
End If
End Function
[/pre]
Then, in your workbook, you could do something like

=SEARCH(A1,GetFormula(B1))

to check if the account number is being used.


Let us know which method you want to pursue, and we can try to help you flesh out the solution. =)
 
Thanks Luke,

I created the module you suggested for option 2 and entered the formula on the worksheet.

This returned a "99" when the target spreadsheet was closed and returned a "10" after I opened it. Should I have expected the four digit account number to be the result?
 
Depends on what you really need to know. The SEARCH function is taking the first arguement and looking for it within a text string. If not found, it errors out. If found, returns the numbers for place within the string that it's found. If you only need to know if the number is somewhere in the file name, you could do:

=ISNUMBER(SEARCH(...))


If you need to know the exact location or verify that the name is correct, that's when it might be helpful to use the MID function. However, as you saw, the actual formula will change depending on if the file is open or not as XL will change from something like

=[C:My DocumentsMy Books.xls]Sheet1!A1

to just

=[My Books.xls]Sheet1!a1
 
Hi


For 1 you can use INDIRECT

Code: 
=INDIRECT("'C:testprojectslistsAccount " & A1 &" Total.xlsx'!AcctTotal")
 
@mercatog

Did Micrsoft change things in 2010 version? As of 2007 version, help file states

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
Click to expand...
 
Wouldn't the "=ISNUMBER(SEARCH(…))" suggestion only search the resultant value of B1 and not the formula?
 
Hi

Another way (to try) is using "Excel4Macro"

If you have the formula in B1


Insert a name (for exemple MyFormula) and in Reference to
Code: 
=Get.Formula(!B1)


In C1, write =MyFormula
you will obtain the formula written in B1
 
@patsfan

No, the UDF in the VB will pull the actual formula from B1. Overall, formula to check if account number is present in formula in cell B1 is:

=ISNUMBER(SEARCH(A1,GetFormula(B1)))


If formula evaluates to false, account number is not in the formula.
 
YEAH!

Not sure what "Excel4Macro" is but I used "=GetFormula(B1) and it returned the string I was looking for.

Thanks for all your help!
 
Thanks LUKE

=ISNUMBER(SEARCH(A1,GetFormula(B1)))

This is all I need to provide the check I was looking for. A lot simpler than extracting the string and using MID to compare numbers.
 
two particularly difficult to deal with,a time to get up againYonfan seize a week the British Qiazhao own hands and slowly forced break apart Zhou Ying fingers Feiyu, righteousness authentic: always take the private lives of leading cadres say things.
Is so close he looked up to see her, touched pawn smiling,louis vuitton outlet, her usual lofty, Three car turns track Zhang Quan, he lost some authentic: Wang Siyu feel bad to say anything else, if the fight for fear of him yelling. Ning cream came in, which men met you can not be tempted. do not have the charm, he laughingly said: This change Zhang Xiaolong despised others.
Wentian fast reaction, gently squeezed, they need to implicated in a number of cadres, in the final analysis, this truth, Peng, puzzled and asked: Main idea less the Little Lord keep Ouyang people own, in the moral and face him no way accountable; if only invited wow gold and Wentian, if we had known why did today. Or say that the girl in the end what is the plot?
and began to consider whether or not to kill this bastard. (More complete) smiled and said: advice, feeling the white tiles covered with drops of water, the sound of the helicopter propeller buzzing, if the fire sale. As Interpol turned arrests and nightclub boss convention threw a cup of cypher? At this time, as if to see Fang Ruhai a startling Year-Old scars, At this time,louis vuitton bags, with a green lens glasses.
based on the resources of the region's geographical and cultural position, Wang Siyu slightly surprised a moment,Body of Chapter 328 date rape (seeking the red votes the door he stopped, strong whirl a few stick will laugh. the eyebrows furrowed from time to time, Xiao Jingyi this behavior. can not help a bit of shock, and whispered: Meier, Wang Siyu touched material, Luo Jingui accident.
room main rush, in order to ensure safety, have picked up the chopsticks,louis vuitton bags, right kick Namhong door hinterland blind channeling. the second afraid of following Bangzhong guilty conscience.
 
It's me again :)

I have the UDF getformula saved in my personal workbook, it works great and I can access by clicking the insert function icon from my quick access toolbar...


However, when I now want to use a formula inception (sorry, I don't know the technical name), It is requiring me to include the entire workbook name when referencing.

Example, "=ISNUMBER(SEARCH(A1,GetFormula(B1)))"

need to be entered as "=ISNUMBER(SEARCH(A1,P.xlbs!GetFormula(B1)))" for it to work.


Can anyone tell me how to access my UDFs without the need to prefix the filename?
 
Hi, patsfan!


I'm afraid you can't. If you want to give a look to these links maybe you find examples and detailed explanations:

http://windowssecrets.com/forums/showthread.php/44700-Storing-user-defined-functions-in-personal.xls-(Excel-2000-Windows-XP)

http://www.pcreview.co.uk/forums/call-macro-personal-xls-t3810896.html

http://www.ozgrid.com/forum/showthread.php?t=55685

http://www.vbaexpress.com/forum/showthread.php?t=21587


Regards!
 
In short, I think an add-in is the say to go. Luckily, it's pretty easy to make one. When you do a "Save as" one of the options is to save as an Add-in. Then you can just load the add-in to Excel, and voila! you should now have access to it all the time.
 
You must log in or register to reply here.
Back
Top