+ Reply to Thread
Results 1 to 10 of 10

Using INDEX to prevent deleted columns from causing REF errors in forumal

  1. #1
    Registered User
    Join Date
    02-12-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    58

    Using INDEX to prevent deleted columns from causing REF errors in forumal

    Hi all, I am trying use INDEX a range (J40:AR40) to prevent a macro that deletes columns in that range so it doesn't cause formula REF errors. The macro deletes a range of columns between J40:AE40. I have tried every possible idea I have on where to place the INDEX's but with no luck. Any help would be greatly appreciated.


    Please Login or Register  to view this content.
    Thanks
    Alex

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Using INDEX to prevent deleted columns from causing REF errors in forumal

    It's probably not the formula, but the Macro you need to correct...
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Using INDEX to prevent deleted columns from causing REF errors in forumal

    Try this...

    PHP Code: 
    =IF(C40="REMOVE",SUMIF(INDIRECT("$J$39:$AE$39"),"Extended Price",INDIRECT("J40:AE40"))*-1,SUMIF(INDIRECT("$J$39:$AE$39"),"Extended Price",INDIRECT("J40:AE40"))) 


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    02-12-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    58

    Re: Using INDEX to prevent deleted columns from causing REF errors in forumal

    This formula works great but, I have multiple rows with this formula and I cant fill it down. Any suggestions?

    Thank you,

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Using INDEX to prevent deleted columns from causing REF errors in forumal

    Just applying the suggested formula above and down won't affect the logic. Since the ranges were already surrounded by dollar sign so it denotes that the range wont get incremented / decremented.

    Please correct me if my assumption is wrong

  6. #6
    Registered User
    Join Date
    02-12-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    58

    Re: Using INDEX to prevent deleted columns from causing REF errors in forumal

    I am using the dollar signs to set the Range ($J$39:$AE$39) for my criteria ("Extended Price") in my sumif statment. I need the sum range (J40:AM40) to be incremented to continue down the rows... if that makes any sense.

    Thanks

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Using INDEX to prevent deleted columns from causing REF errors in forumal

    Hm.. Now I understand....

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

  8. #8
    Registered User
    Join Date
    02-12-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    58

    Re: Using INDEX to prevent deleted columns from causing REF errors in forumal

    This sort of works... The macro that deletes columns in that range, when run, deletes and shifts the range formula over and eventually causes a REF error.

    If that makes sense.

  9. #9
    Registered User
    Join Date
    02-12-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    58

    Re: Using INDEX to prevent deleted columns from causing REF errors in forumal

    This sort of works... The macro that deletes columns in that range, when run, deletes and shifts the range formula over and eventually causes a REF error.

    If that makes sense.

  10. #10
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Using INDEX to prevent deleted columns from causing REF errors in forumal

    Oops... Posted the formula without testing....

    Just confirm me what is the start cell of the formula (i.e.) the cell on which the formula starts

+ 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