Posted on 2011-10-05
Last Modified: 2012-05-12
I am new to excel 2010, I am sure this didnt used to happen in excel 2003 but here is the problem:

I have a formula in say E10 that looks in cell E10 and E11. this works fine.
But if I delete row 11 the formula in E10 does not work anymore. seems to be putting #REF! in the formula in E10 where it references E11.
I want to be able to delete rows but other formulas referencing this row would still work. Row 12 would now be row 11 if 11 deleted.

Question by:RobJanine

    Author Comment

    formula in E10:
    =(IF($B10=B11, (IF($E10=E11,"IN","OUT")),"OUT"))

    if i delete row 11 this is what the formula now reads:
    =(IF(B10=#REF!, (IF(E10=#REF!,"IN","OUT")),"OUT"))

    LVL 81

    Accepted Solution

    In tables where formulas reference cells above or below the row in which the formula resides, cell references in those formulas get mangled (set to #REF or changed to other cells in other rows) when inserting and deleting rows in the table. This happens because Excel always tries to maintain cell references when making changes to the structure of a worksheet. But this very functionality can lead to undesired results in a table that is frequently manipulated by inserting and deleting rows.

    The most obvious solution to this problem is to not reference specific rows above or below the row in which the formula resides. If this is not possible then use the OFFSET function. For example, if a formula in row 6 references a cell in row 5, column A, replace the reference A5 with OFFSET(A6,-1,0). When formulas are constructed this way Excel will always leave the cell references alone when inserting and deleting rows.


    =(IF($B10=OFFSET(B11,1,0), (IF($E10=OFFSET(E10,1,0),"IN","OUT")),"OUT"))


    Author Comment

    Thanks alot Kevin, thanks for explaing the problem clearly too.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now