Code To Extract Formula From a Cell

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Code To Extract Formula From a Cell

  1. #1
    New Member
    Join Date
    Aug 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Dear All,

    First, I want to wish this board & you all a very happy & prosperous new year.

    My issue, is that I have a cell "B5" contain a link to another WorkSheet in the same WorkBook, the Cell contains (='BABY NEEDS'!AB19), while the WorkSheet "BABY NEEDS" Cell AB19 house a value of $ 550.

    I just want a simple code to tell me the context of Cell "B5" (and not the value of it), or in other words, I want to now what is the function written in Cell "B5" as a text so I can extract the linked Sheet Name by using any of the text functions.

    Can you help me on this?

    Thanks in advance.

    Excello.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In the MoreFunc.xll AddIn from Laurent Longre, there is a function called FORMULATEXT that provides you with what you need. If not, you have to use VBA for this, I don't know of any builtin method in Excel to accomplish this.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is a custom function that will return a cell's formula:

    Code:
    Function CellFormula(Rng As Range) As String
        CellFormula = Rng.Formula
    End Function
    Paste the code into a General module. Then enter this in a blank cell on your worksheet:

    =CellFormula(B5)

    and you will get ='BABY NEEDS'!AB19.


  4. #4
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,858
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try another way ()no VBA :

    Clt + ~ ( Keyboard above of Tab)

    still can get ='BABY NEEDS'!AB19

    Regards
    Bosco

  5. #5
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,858
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try another way no VBA :

    Ctrl + ~ ( Keyboard above of Tab)

    still can get ='BABY NEEDS'!AB19

    Regards
    Bosco

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2003-01-02 10:22, bosco_yip wrote:
    Try another way no VBA :

    Ctrl + ~ ( Keyboard above of Tab)

    still can get ='BABY NEEDS'!AB19

    Regards
    Bosco
    That only displays the formula instead of its result. You cannot use text functions with it.

  7. #7
    New Member
    Join Date
    Aug 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Juan, Andrew

    Thank you all.
    I got the written function in the Cell, but I put myself in another problem where I failed to extract & separate the Department Name "BABY NEEDS" out of the string & have the Cell reference "AB19" alone.

    Also, I have discovered that some linked cells has the "'" at the begining and some dosen't have (I don't know why!!) as follows:

    Case (1):
    "'BABY NEEDS'!AB19"

    Case (2):
    "H.B.A.!AB19" is missing the "'" at the begining but still I got its value.

    Case (3):
    "'COSMETICS & PERFUMES'!AB19" a longer string where I want to get the sheet name "COSMETICS & PERFUME" alone & the Cell Reference "AB19" alone, and the same for other a/m strings.

    I am lost in all text functions. Can you help me on this.

    Thanks.... Excello

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If the formula text is in cell C5, to get the sheet name use:

    =SUBSTITUTE(MID(C5,2,FIND("!",C5,1)-2),"'","")

    and to get the cell reference use:

    =RIGHT(C5,LEN(C5)-FIND("!",C5,1))

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,444
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2003-01-02 11:12, Excello wrote:
    Juan, Andrew

    Thank you all.
    I got the written function in the Cell, but I put myself in another problem where I failed to extract & separate the Department Name "BABY NEEDS" out of the string & have the Cell reference "AB19" alone.

    Also, I have discovered that some linked cells has the "'" at the begining and some dosen't have (I don't know why!!) as follows:

    Case (1):
    "'BABY NEEDS'!AB19"

    Case (2):
    "H.B.A.!AB19" is missing the "'" at the begining but still I got its value.

    Case (3):
    "'COSMETICS & PERFUMES'!AB19" a longer string where I want to get the sheet name "COSMETICS & PERFUME" alone & the Cell Reference "AB19" alone, and the same for other a/m strings.

    I am lost in all text functions. Can you help me on this.

    Thanks.... Excello
    If you are using FORMULATEXT...

    =SUBSTITUTE(WMID(FORMULATEXT(A1),1,1,"!"),"=","")

    =WMID(FORMULATEXT(A1),2,1,"!")


    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    New Member
    Join Date
    Aug 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks to Andrew/Aladin,

    I managed to get what I want if I inserted (edited) these functions directly to a cell, but I failed to get the same thru VBA code. Would you please help me on how to right a code to get me the department alone & cell reference alone ..

    Many Many Thanks to all of you.
    Excello..

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com