+ Reply to Thread
Results 1 to 5 of 5

INDIRECT returning #REF error when trying to convert string to formula

  1. #1
    Registered User
    Join Date
    04-10-2012
    Location
    New York City
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    10

    INDIRECT returning #REF error when trying to convert string to formula

    Hello,
    I have a complicated formula which basically returns a text string that looks something like this:

    48000/1000

    Now I just want to actually calculate this value. I have tried

    =INDIRECT(formula)

    but that returns a #REF error, and I can't figure out why, since there are no errors in the string itself.
    I was hoping someone here might have ideas?

    If you are interested in the details of the formula that generates the string, here it is:

    TEXT(INDEX('recode ranges'!$A:$BG,MATCH('reformat numerical'!$A2,'recode ranges'!$A:$A,0),MATCH('reformat numerical'!K$1,'recode ranges'!$1:$1,0)),"General")&'rules for num format'!A3)

    where the INDEX function returns the value which would be 48000 in the string example given above at the top of this post, and where the 'rules for num format'!A3 contains the text "/1000".

    Thanks in advance for any help!

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: INDIRECT returning #REF error when trying to convert string to formula

    indirect doesnt evaluate text
    if its only going to be in one cell try
    insert name define new name call it say "mycalc"
    in refers to put
    =evaluate(TEXT(INDEX('recode ranges'!$A:$BG,MATCH('reformat numerical'!$A$2,'recode ranges'!$A:$A,0),MATCH('reformat numerical'!$K$1,'recode ranges'!$1:$1,0)),"General")&'rules for num format'!$A$3))
    now try =mycalc
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    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,477

    Re: INDIRECT returning #REF error when trying to convert string to formula

    It might be easier to offer suggestions if we could see the context that you are using this in?

    Maybe something like this though...
    =48000/MID("/10000",2,99)
    =INDEX('recode ranges'!$A:$BG,MATCH('reformat numerical'!$A2,'recode ranges'!$A:$A,0),MATCH('reformat numerical'!K$1,'recode ranges'!$1:$1,0)),"General")/mid('rules for num format'!A3,2,99)
    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

  4. #4
    Registered User
    Join Date
    04-10-2012
    Location
    New York City
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    10

    Re: INDIRECT returning #REF error when trying to convert string to formula

    Thanks, martindwilson and FDibbins, for your quick replies! I used FDibbins suggestion and it worked.

    The larger context is that I need to be able to create dynamic formulas where data in a spreadsheet is automatically adjusted by specific formulas (multiplied or divided by a certain factor, for example) if the name of the column in which the data appears is on a particular list. The type of adjustment to each data point depends on what column it is in, and I need it to be flexible so that I can use it with a bunch of different large datasets. I know there is a way to do this kind of thing with Macros, but I prefer to avoid them as my programming skills in that area aren't great, and I tend to have more trouble with Macros when moving from computer to computer (and I work on a bunch of different ones).

    Thanks again for your help!

  5. #5
    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,477

    Re: INDIRECT returning #REF error when trying to convert string to formula

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

+ 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] Indirect() formula returning #Ref error but cant see why
    By rikkyshh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-23-2013, 10:03 AM
  2. SUMIF with INDIRECT.EXT returning #VALUE error
    By opex in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-14-2013, 10:34 AM
  3. Convert my formula to indirect???
    By CBRO.IOM in forum Excel General
    Replies: 2
    Last Post: 08-02-2011, 09:38 AM
  4. Help using INDIRECT with formula string
    By DelMonte in forum Excel General
    Replies: 4
    Last Post: 01-05-2009, 08:31 AM
  5. putting a string from one cell in the formula of another -- indirect needed?
    By alfrodull@gmail.com in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2006, 04:00 PM

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