+ Reply to Thread
Results 1 to 19 of 19

how to refer to the formula (not the result of the formula) typed in another cell

  1. #1
    Registered User
    Join Date
    01-28-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Question how to refer to the formula (not the result of the formula) typed in another cell

    Please help me if you can:

    In one work sheet "DATA"! I have a list of number 1,5,8,134,54,23 These numbers are in A1-A6

    In Another worksheet within the workbook "SUMMARY"! I have multiple cells in Column D pointing to these cells:

    ie D1-D6 the cells are ="DATA"!A1 - "DATA"!A6

    in column L in "SUMMARY" I am trying to find the row of the data in column D of "DATA"! that is being reference by the cells in Column C and write this new formula in such a way that I can use the fill function in excel to drag the equation:

    ie Within Cell L1 I want to write an equation "Row(" where the formula refers to C1, but knows to give me the row number of the values in Column D of the "DATA" sheet.

    thank you for your help

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    Hi,

    I for one don't understand precisely what you're asking for.

    Please upload an example workbook and be sure to add the results you expect to see, and a note as to how you have derived them.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-28-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    Okay I attached a file
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    Well, if you know what the result of the formula is, can use a combination of Address and Match.

    For instance:
    =ADDRESS(MATCH(B1,$A$1:$A$100,0),3,4,1)

    This will give me the address in Column C, where its relative cell in column A will match what's in Cell B1.

    So if you know what the result is, you can try to match it in a range of cell, and use address to specify the offset.
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  5. #5
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    This will give you the row number:
    =MATCH(A1,DATA!$A$1:$A$10,0)

    This will give you the actual cell reference:
    =ADDRESS(MATCH(A1,DATA!$A$1:$A$10,0),1,4,1)

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    Hi,

    Why not just

    Please Login or Register  to view this content.
    or with an unsorted list in Data

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-28-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    The issue with the match function is that I have many cells in the column with the same values and match will only give me the first one.

  8. #8
    Registered User
    Join Date
    01-28-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    In my actual data sheet Summary!A1-A10 are referring to specific values and many of these values at any given point of time could have the same weight such as A1,A3,and A7 all being equal to 1%

  9. #9
    Registered User
    Join Date
    01-28-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    I tried creating a cheat column with this module:

    Function CellFormula(c)
    CellFormula = c.Formula
    End Function

    and the entering cellformula(A1) and then I tried to insert this text into the row formula using another equation, but it is not working and is still returning the row reference of A1 in SUMMARY and not the row of the cell formula reference.

  10. #10
    Registered User
    Join Date
    01-28-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    I could get this to work, but I don't know how to remove the "=" from the result.

  11. #11
    Forum Contributor
    Join Date
    04-01-2009
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    280

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    Here is an example that uses an Excel4 macro (in the Named Ranges) to get the formula and then Indirect to get the Row.

    Not pretty, but maybe it is a starting point for someone else...
    Attached Files Attached Files

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    Hi,

    In that case how do you want to resolve this conflict. i.e. What rule do you want to apply in this event?

    Regards

  13. #13
    Registered User
    Join Date
    01-28-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    I am not entirely sure, I felt that by removing the = sign I could use the text of the cell as the input in the other cell formula.

  14. #14
    Registered User
    Join Date
    01-28-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    Is it possible to use something along the lines of:

    =ROW(INDIRECT("'DATA'!A"&D1))

    although for some reason this equation as is, is returning the exact value shown in D1 or basically it is doing the same thing as typing into the cell =D1
    Last edited by ZMBER; 05-17-2011 at 12:24 PM.

  15. #15
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    Have you not tried the answer I gave you a day or so ago? viz.

    Please Login or Register  to view this content.
    copied down as appropriate.

    There's no need for Indirect() which being a volatile function can give unexpected results.

    Regards

  16. #16
    Registered User
    Join Date
    01-28-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    I appreciate your help.

    The issue with Match functions as I said before is that I have a lot of values that repeat themselves and the match function returns the row number of the first instance of the data, not the exact corresponding row.

    thank you

  17. #17
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    Hi,
    That is indeed true, as you said last time, but you didn't answer my follow up question about what you want to do when there are duplicates. In that event which row would you want to show. The first, last, some other.....????

    Regards

  18. #18
    Registered User
    Join Date
    01-28-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    I created a solution using some helper column:

    I first needed two module:

    Function MyEval(s)
    MyEval = Evaluate(s)
    End Function

    Function CellFormula(c)
    CellFormula = c.Formula
    End Function

    Source: Mr. Excel forums

    Then I used

    =cellformula()

    to create a helper column in C1 that would give me the underlying Equation of A1 and then dragged this down

    Then In D1 or another column I used

    =myeval("=Row("&"=Right(C1,13)"&")")

    thank you guys for all your help. I am sure I am not the only one that needs so I hope someone else finds this helpful

  19. #19
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: how to refer to the formula (not the result of the formula) typed in another cell

    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

    Read this to understand why we ask you to do this

    You really should have told us in advance that you were cross posting for all the reasons mentioned in the links above.

    If you post the same request to this and other forums again please remember to do so. None of us appreciate wasting our time which is freely given and which could otherwise be helping someone else.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1