+ Reply to Thread
Results 1 to 9 of 9

?Offset and formulatext functions?

  1. #1
    Registered User
    Join Date
    05-22-2014
    Posts
    22

    ?Offset and formulatext functions?

    Hi,

    I'm having trouble figuring out how to get excel to work with me here. I have attached an example workbook showing what I'm trying to do. If someone could explain to me how to solve this I would be very thankful.

    I'm trying to construct a summary table of a large worksheet. I have a value (given in cell G4). Now my summary table begins at cell J7 with a copy of the value in G4. ok easy (this part i have to manually select for each of the data subsets due to slight varaition in entries to each subset). Next in under "result 1" (cell K7) I have used formulatext and RIGHT functions to extract the text cell information from the original location of my date value. In "result2" (cell L7) I have used offset function to pull data from a cell (E7) near to the original location of my date value. Now what I can't seem to figure out is using both offset and formulatext (as well as RIGHT) to pull data from cell (E7). "Result3" show what I've been trying to use, but excel keeps telling me that the formula is incorrect. What am I missing here?

    Thank you,

    -Michael
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: ?Offset and formulatext functions?

    Cross-post.

    http://www.mrexcel.com/forum/excel-q...functions.html

  3. #3
    Registered User
    Join Date
    05-22-2014
    Posts
    22

    Re: ?Offset and formulatext functions?

    I could not upload my attachment on that website.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: ?Offset and formulatext functions?

    They do not allow uploads - but you still need to tell us (and them) where else you have posted your question
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,840

    Re: ?Offset and formulatext functions?

    There is nothing in O5
    Ben Van Johnson

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: ?Offset and formulatext functions?

    You have told us what formulas you have tried to use to do this or that - but perhaps explain exactly what you are trying to do (forget formulas and stuff...what do you want to get, and where is it?)

  7. #7
    Registered User
    Join Date
    05-22-2014
    Posts
    22

    Re: ?Offset and formulatext functions?

    Sorry about the typo in my example sheet. it should have read: =offset(RIGHT(FORMULATEXT(J7),2),3,-2).

    General idea
    I would like to use offset function (at M7) to reference a cell near to the cell that I have previously referenced (at J7).


    Thank you for your help thus far.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: ?Offset and formulatext functions?

    No, tell us what you are trying to do, not what formula you are trying to use

  9. #9
    Registered User
    Join Date
    09-19-2016
    Location
    San Francisco Bay Area
    MS-Off Ver
    Office 2013
    Posts
    1

    Re: ?Offset and formulatext functions?

    I just had this problem. This works for me (Office 2013):

    The Indirect function is needed to convert the text to a reference. You also need the length of the string, too. For example, this worked for me in my application:

    =OFFSET(INDIRECT(RIGHT(FORMULATEXT(U266),LEN(FORMULATEXT(U266))-1)),0,1)

    I think yours would be

    =offset(INDIRECT(RIGHT(FORMULATEXT(J7),2)),3,-2)

+ 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] Combining IF, OFFSET and INDIRECT functions.
    By dowell89 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-30-2013, 05:50 AM
  2. Offset and find functions
    By maninjapan in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-01-2011, 07:16 AM
  3. Excel 2007 : Offset and Min functions
    By nrobertson1 in forum Excel General
    Replies: 3
    Last Post: 11-29-2011, 01:04 AM
  4. Lookup/offset functions
    By Nyman in forum Excel General
    Replies: 2
    Last Post: 01-04-2010, 12:09 PM
  5. Charting Offset Functions
    By thule125 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-04-2008, 04:24 PM
  6. Trying to use sumif and offset functions
    By hizzle in forum Excel General
    Replies: 5
    Last Post: 02-09-2006, 07:47 PM

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