+ Reply to Thread
Results 1 to 7 of 7

find and replace for indirect

  1. #1
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    find and replace for indirect

    Hello. I have these formulas (there are 100+ but all of the same pattern) down a column.

    =SUM('ACTUALS detail'!AM72:AQ72)
    =SUM('ACTUALS detail'!AM73:AQ73)
    =SUM('ACTUALS detail'!AM87:AQ87)
    =SUM('ACTUALS detail'!AM92:AQ92,'ACTUALS detail'!AM93:AQ93)

    How can I do find and replaces to make the formulas like this:
    =SUM(Indirect("'ACTUALS detail'!AM72:"&AT&"72")
    and for the ones with multiple selections
    =SUM(Indirect("'ACTUALS detail'!AM92:"&AT&"92,'ACTUALS detail'!AM93:"&AT&"93")

    I don't want have to do that manually for each cell. Thanks.

  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: find and replace for indirect

    Why do you need INDIRECT? Those formulae will copy down incrementing the row number.
    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
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: find and replace for indirect

    Richard Buttrey is correct. The INDIRECT function isn't needed.

    Write the second set of formulae just like the first set. You only need 2 formulae. After entering the first formula where you want it, select the cell with the formula and with the cursor "grab" the little square on the bottom right of the the selected cell, left click and hold and drag down the column filling all the cells with the formula. The formula references will be updated with each row. Do the same with the second formula.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you are actually wanting to change the first formula into the second formula (INDIRECT IS NOT NEEDED) with Find and Replace, this is how it is done. Select the cells that you want affected by the Find and Replace (in case there are other formulae with the same references that you don't want to change).

    Go to Find & Select, Replace and enter like this:

    Capture.JPG
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: find and replace for indirect

    I need to change it to indirect because the values were hardcoded and I don't want to have to do a search and replace every time I want to change the values. AT in this case is a value of a column that changes automatically so if I indirect it, the cell formulas will reference that. Make sense?

  5. #5
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: find and replace for indirect

    Somebody else recommends this but I get the following error after I try the first find and replace "the formula you tried has an error"...and it won't complete the replace.

    It looks like three times will do it...

    Find:
    SUM(
    Replace with:
    SUM(INDIRECT("

    Find:
    :AQ
    Replace with:
    :"&AT&"

    Find:
    )
    Replace with:
    ")

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: find and replace for indirect

    If you are using that approach, you need to replace the = with something 1st, that way, it is no longer a formula
    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

  7. #7
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: find and replace for indirect

    @ FDibbins. You're a mofuggin genius THanks.

+ 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] Find and replace where contents a formula so can't be seen by Find and replace.
    By markDuffy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2015, 02:24 PM
  2. Replies: 16
    Last Post: 12-02-2014, 03:38 PM
  3. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  4. find in excel replace in word: find/replace text in text boxes and headers
    By dean.rogers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 12:40 PM
  5. Replace =INDIRECT
    By BRISBANEBOB in forum Excel General
    Replies: 4
    Last Post: 12-27-2011, 04:38 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