Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Keep " IF " formula working after inserting cell (https://www.askmehelpdesk.com/showthread.php?t=743912)

  • Apr 12, 2013, 02:46 AM
    dannac
    Keep " IF " formula working after inserting cell
    Worksheet to keep track of mail route. Sample sheet below will have approx 900 boxes.

    Cols B, E, H, etc will input actual box number.
    Cols A, D, G, etc keep numerical order of boxes.

    Row 2 at cells C2, F2, I2, etc will show number of boxes on that street.


    Some of the formulas used

    A4 =IF(B4="","",A3+1)
    D4 =IF(E4="","",D3+1)
    G4 =IF(H4="","",G3+1)

    D3 = =MAX(A3:A37)+1
    G3 = =MAX(D3:D37)+1

    http://i819.photobucket.com/albums/z...psfff4cc51.jpg

    Problem comes when I need to insert a new box number between two others.
    Say on Waverly Rd... I need to put box 140 between 138 and 144.

    Inserting cells throws all the "if" formulas off, and I would have to do some
    copying down the entire column again.

    Appreciate some ideas or fixes.
  • Apr 12, 2013, 03:16 AM
    ScottGem
    Since you can't insert a row, you have to copy and paste. So you copy and paste from 144 down one row just in Col E. The formulas in Col D should be unaffected.
  • Apr 12, 2013, 06:31 AM
    dannac
    Thanks ScottGem... was hoping for something better than copy and paste.

    Also got a lesson today on "cross posting", which I thought meant asking the same question in different sections of the same forum. But it's posting same question two different forums.

    I'm suppose to provide links.
    Keep "IF" formula references after inserting cell(s)

    Keep " IF " formula working after inserting cell
  • Apr 12, 2013, 09:32 AM
    ScottGem
    Many people do monitor multiple forums. I've seen questions asked on different sites and never seen anyone complain about them. But I guess ExcelForum doesn't like it,

    But it seems that no one else knows of a better solution then copy and paste.

  • All times are GMT -7. The time now is 11:48 PM.