+ Reply to Thread
Results 1 to 7 of 7

Replace absolute cell reference with Indirect cell reference in formula

  1. #1
    Registered User
    Join Date
    04-10-2013
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    4

    Replace absolute cell reference with Indirect cell reference in formula

    Hello all,

    I've been looking for a way to get my excel workbook somewhat fool-proof (impossible, probably).
    It is an workbook ranging 5 sheets in which 1000 lines of code need to be rearranged and adapted to come up with something my program will understand.
    In the pursuit of making it fool proof, I found the INDIRECT formula to help protect against cut/copy/paste errors and I want to incorporate it in all my formulas, but doing it manually will require me to go over 14 columns with 1000 rows.
    I made an exert in a new workbook, see attachment.
    In cell L3, I started with replacing the cell reference with A3 into (INDIRECT("'Structure'!A3")),
    M3 from =IF(G3="NLP",B3,("'"&B3&"'")) into =IF((INDIRECT("'Structure'!G3"))="NLP",(INDIRECT("'Structure'!B3")),("'"&(INDIRECT("'Structure'!B3"))&"'"))
    ETc etc, completely for the range L3:W1003.

    But after completing the first line, autocomplete couldn't help me because the reference is between quotation marks, so it doesn't continue a count.
    Macro recording didn't get what I was doing, so couln't complete it either. Using Macro's already online all tried putting INDIRECT function in front of the formula, but also that results in a REF! error.

    Basically, I'm a little lost and spent my entire day on just this issue...

    Hopefully you can help me out, give me a solutions, an alternative or w/e.
    Attached Files Attached Files
    Last edited by Roothy; 04-11-2013 at 03:27 AM. Reason: solved thanks to TMS

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,732

    Re: Replace absolute cell reference with Indirect cell reference in formula

    Maybe:

    ="'"&(INDIRECT("'Structure'!A"&ROW(L3)))&"'"

    and
    =IF((INDIRECT("'Structure'!G"&ROW(M3)))="NLP",(INDIRECT("'Structure'!B"&ROW(M3))),("'"&(INDIRECT("'Structure'!B"&ROW(M3)))&"'"))

    etc.


    Regards, TMS

  3. #3
    Registered User
    Join Date
    04-10-2013
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Replace absolute cell reference with Indirect cell reference in formula

    Hey TMS,

    Thanks for your reply.
    I tried to code you suggested but it failed/returned a REF! error. Probably because the reference to the sheet and cell is between quotation marks?
    I'm still working on sorting through the evaluation thing, to see where it fails precisely, but it what i mentioned above seems to be the first part to return a REF! error.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,732

    Re: Replace absolute cell reference with Indirect cell reference in formula

    You said you wanted to be able to drag the formulae down. The constructs above allow that to happen. However, what it finds in the cell is what it finds in the cell. I suggest you use Evaluate Formula to see what the formula tries to do.

    It would probably help if you gave some actual data and the static formulae that work.

    Regards, TMS

  5. #5
    Registered User
    Join Date
    04-10-2013
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Replace absolute cell reference with Indirect cell reference in formula

    Hey TMS,

    Thanks again, I think I must be the one doing something wrong then. I'm going to retry and see what I'm doing wrong with pasting your formula. (hard to make things fool-proof against yourself :P )
    I don't think I have the file on this computer, so I'll get back to it. If you say it should work, then I'm sure you'll be right.

    Best,

  6. #6
    Registered User
    Join Date
    04-10-2013
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Replace absolute cell reference with Indirect cell reference in formula

    Hey TMS,

    As expected, you were completely right. I wasn't looking correctly anymore, yesterday.
    I pasted it again (in the right cell this time) and it worked splendidly.
    You saved me a ton of work, thank you!

    Best,
    Roothy

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,732

    Re: Replace absolute cell reference with Indirect cell reference in formula

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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