+ Reply to Thread
Results 1 to 6 of 6

Formula to extract an absolute location contained in another formula

  1. #1
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    117

    Formula to extract an absolute location contained in another formula

    I have a column that contains a formula that includes an absolute reference to a cell on another worksheet.

    sample formula:
    Please Login or Register  to view this content.
    I need to create another column that lists the absolute reference that is in that formula. (i.e. 'Recipes Entry'!$AA$8). Is there a way to do that?

    I know that I can use FORMULATEXT to get the full formula, but I just need the cell reference part of the formula.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,163

    Re: Formula to extract an absolute location contained in another formula

    Somehow I suspect there is an easier way to do this, but how about...

    =SUBSTITUTE(MID(FORMULATEXT(D4),FIND("'",FORMULATEXT(D4))+1,LEN(FORMULATEXT(D4))),")","")
    Last edited by jeffreybrown; 07-31-2019 at 06:39 PM.
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    117

    Re: Formula to extract an absolute location contained in another formula

    Thanks for the superfast reply, Jeff! It almost works perfectly. It cuts off the leading ' in the reference. What can I change to get that included?

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,163

    Re: Formula to extract an absolute location contained in another formula

    Sorry about that. Remove the +1

  5. #5
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    117

    Re: Formula to extract an absolute location contained in another formula

    Stupendous! You saved me so much time. Thanks again for you help.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,163

    Re: Formula to extract an absolute location contained in another formula

    Glad it helped you and thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to lookup data contained in a matrix
    By The_Snook in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-16-2019, 11:17 PM
  2. [SOLVED] Extract Words Not Contained in Columns
    By newbieexceldude in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-12-2017, 03:00 PM
  3. VBA to jump to range in contained in a SUMIFS formula
    By johnsspillane in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2016, 01:49 PM
  4. [SOLVED] VBA to create a relative formula in a conditional format (rather than an absolute formula)
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2016, 08:00 AM
  5. [SOLVED] Looking for a formula that indicates text not contained in a list.
    By Travisty in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-20-2015, 09:58 AM
  6. Evaluate a formula contained in a different cell
    By godonnell06 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-12-2015, 03:49 PM
  7. Replies: 0
    Last Post: 04-08-2013, 05:08 AM

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