+ Reply to Thread
Results 1 to 10 of 10

Inserting/deleting rows and cutting cells causing corrupted formulas.

  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Inserting/deleting rows and cutting cells causing corrupted formulas.

    Hey all,

    Whenever you insert / delete row or cut cells out in Mon/Tue sheets it's causing issues in the table in the Weekly view tab.

    Is there any way to prevent it? I don't mind redoing whole spreadsheet, it was done ages ago in a very fast manner.

    I gave you an idea by deleting a row range in Mon tab which causes #REF error.

    Thanks,
    Al
    Attached Files Attached Files

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Inserting/deleting rows and cutting cells causing corrupted formulas.

    What I see I removed Wed-Friday tabs and no sign of REF in Monday part of the table
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,694

    Re: Inserting/deleting rows and cutting cells causing corrupted formulas.

    Hi,

    It's hard to say as we can't see what your references were before you deleted some rows.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

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

    Re: Inserting/deleting rows and cutting cells causing corrupted formulas.

    its further over chaps! look at aa
    however some of it seems oddly constructed but to stop it happening
    change cell referenced to index
    so
    =IF(Mon!$V7="50/50",Mon!S7,IF(Mon!$V7="D","",IF(Mon!$V7="W",Mon!S7,""))) becomes

    =IF(INDEX(Mon!$V:$V,ROWS($A$1:A7))="50/50","50/50",IF(INDEX(Mon!$V:$V,ROWS($A$1:A7))="W","W",IF(INDEX(Mon!$V:$V,ROWS($A$1:A7))="D","D","")))

    INDEX(Mon!$V:$V,ROWS($A$1:A7)) is the same as Mon!$V7
    "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

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,539

    Re: Inserting/deleting rows and cutting cells causing corrupted formulas.

    Here is an implemented solution that takes a similar approach to martindwilson but is a little different. The solution has to deal with all five columns (or is it six, I don't remember now).

    The problem, alxes, is that you have big, bold #REF! errors right in our face but the little tiny #REF! errors off to the right are the ones you want us to look at. It would help to be more specific.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  6. #6
    Registered User
    Join Date
    07-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Inserting/deleting rows and cutting cells causing corrupted formulas.

    Sorry guys for not being specific enough. The original spreadsheet was 2 MB and I had to throw away a lot of stuff to get it below 1 MB to post it on the forum and that created some problems.

    Thanks for the proposals, I will look into them when I got few minutes.

  7. #7
    Registered User
    Join Date
    07-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Inserting/deleting rows and cutting cells causing corrupted formulas.

    Here is an implemented solution that takes a similar approach to martindwilson but is a little different. The solution has to deal with all five columns (or is it six, I don't remember now).
    I like that solution, the only problem is that it is pulling the initials to the root cause table even if the root cause column (Column V, Mon tab) is left blank. I think it's connected with this part of the formula:

    Please Login or Register  to view this content.
    Thanks,
    Al
    Last edited by alxes; 12-12-2013 at 12:36 PM.

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,539

    Re: Inserting/deleting rows and cutting cells causing corrupted formulas.

    I did another review of your spreadsheet, understand a little more about what you're actually doing, and now wonder why the original formulas were so complicated. For example, the original formula in column AA is

    =IF(Mon!$V6="50/50","50/50",IF(Mon!$V6="W","W",IF(Mon!$V6="D","D","")))

    But according to header in Mon!V5, the only possible values are 50/50, W, D, or null. Why do you check each possible value here? Basically this says if the user enters an illegal value, show a null. If you want to restrict what the user can enter, then I would recommend Data Validation on Mon!V5. I have done that and this simplifies the other formulas quite a bit. This also solves the problem that you noted.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Inserting/deleting rows and cutting cells causing corrupted formulas.

    Quote Originally Posted by 6StringJazzer View Post
    I did another review of your spreadsheet, understand a little more about what you're actually doing, and now wonder why the original formulas were so complicated. For example, the original formula in column AA is

    =IF(Mon!$V6="50/50","50/50",IF(Mon!$V6="W","W",IF(Mon!$V6="D","D","")))

    But according to header in Mon!V5, the only possible values are 50/50, W, D, or null. Why do you check each possible value here? Basically this says if the user enters an illegal value, show a null. If you want to restrict what the user can enter, then I would recommend Data Validation on Mon!V5. I have done that and this simplifies the other formulas quite a bit. This also solves the problem that you noted.
    Thank you 6StringJazzer, it looks like my main problem was not knowing the INDEX function. I amended the formula proposed by you and it works like a charm.

    Please Login or Register  to view this content.
    martindwilson also gets the credit for help, he came up with INDEX function as well.

    Problem solved.

  10. #10
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,539

    Re: Inserting/deleting rows and cutting cells causing corrupted formulas.

    Glad to hear it's fixed, thanks for marking Solved, and 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)

Similar Threads

  1. Need Help-Error When Deleting or Inserting Rows
    By krisels in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-11-2012, 04:52 PM
  2. Inserting and Deleting Rows & 3-D Summing
    By markvdh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-02-2011, 04:57 AM
  3. Inserting, Cutting, and Pasting Rows Based on What's in a Cell
    By 4EverLearning in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-05-2010, 04:42 PM
  4. Disable macro when inserting/deleting rows
    By aznprod517 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-29-2009, 09:40 AM
  5. Deleting and inserting rows
    By blurtoad in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2007, 04:54 AM

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