+ Reply to Thread
Results 1 to 10 of 10

If formula not updating cell reference when insert row

  1. #1
    Registered User
    Join Date
    02-07-2022
    Location
    Australia
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    3

    Question If formula not updating cell reference when insert row

    Cross-posted at https://www.mrexcel.com/board/thread...t-row.1195229/

    Hi all,

    I'm a new member so forgive me if this has been answered elsewhere but I have scoured the internet in search of an answer to my problem but I am yet to come up with a solution.

    Basically the problem is this. I have an IF formula that starts in AH4 that runs the length of Column AH from rows 4 to the last row. See below.

    =IF(IF(AND(D4="",N4=""),"",IF(D4<>"",D4,AH3))="Type","",IF(AND(D4="",N4=""),"",IF(D4<>"",D4,AH3)))

    This formula works fine for what I need it to do and updates all the cells referenced in it as it moves down the row. i.e D4 becomes D5 and AH3 becomes AH4.

    The part where I seem to be coming unstuck is when I insert a row into the table.
    The formula updates itself in the new row but only updates the cell references for the D cells and not the AH cell references.
    For example D4 become D5 but AH3 remains as AH3 when i need to to update the reference to AH4.

    At present, the only way to fix it is to click "restore to calculated column formula" and as this is a shared spreadsheet with a few people that are not so computer literate, I don't want that to be the only solution.

    Could this be because the formula is in the AH column? Is this some strange quirk of excel?
    Open to any suggestions or possible work around anyone may have.

    Thanks in advance,
    J-Finity.
    Last edited by 6StringJazzer; 02-07-2022 at 10:10 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: If formula not updating cell reference when insert row

    Where are you inserting a new row?

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,314

    Re: If formula not updating cell reference when insert row

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

    I suspect that you need to adjust the first formula, assuming it is in a Structured Table, so that it takes into account the row.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2410 (Windows 11 23H2 64-bit)
    Posts
    87,392

    Re: If formula not updating cell reference when insert row

    No - please post a workbook that you have cut down and desensitised to show the issue.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    02-07-2022
    Location
    Australia
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    3

    Re: If formula not updating cell reference when insert row

    Sorry again. I'm completely new to posting on the forum. See attached sample sheet.
    Attached Files Attached Files
    Last edited by J-finity; 02-07-2022 at 07:20 AM. Reason: A few of the cell references were erroring in the previous attachment

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: If formula not updating cell reference when insert row

    Try this In AH3 copy down
    Please Login or Register  to view this content.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: If formula not updating cell reference when insert row

    Try this also.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-07-2022
    Location
    Australia
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    3

    Re: If formula not updating cell reference when insert row

    Quote Originally Posted by kvsrinivasamurthy View Post
    Try this In AH3 copy down
    Please Login or Register  to view this content.
    This formula works perfectly! You're a life saver! I had to change the D10 cell reference to D4 to get it to work but other than that it works exactly how I need it to. Any idea why my first formula was not working and why the cell reference wasn't updating? I'd like to avoid this issue if possible when writing formula in the future.

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,804

    Re: If formula not updating cell reference when insert row

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    I have added the link to post #1 because you are a new member. Please become familiar with the rules before posting again.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: If formula not updating cell reference when insert row

    Can you give what formula you want to have in the file.

+ 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. Replies: 11
    Last Post: 12-17-2018, 02:17 PM
  2. Incrementing cell reference within formula upon a new row insert within a table
    By m_arkle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-30-2014, 04:16 PM
  3. trying to drag down a formula, but the cell reference # not updating
    By fcbtim in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-27-2014, 02:54 AM
  4. Replies: 3
    Last Post: 08-28-2013, 09:26 AM
  5. [SOLVED] Formula Updating - Changing Cell Reference
    By Nitsirk82 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-29-2012, 08:05 AM
  6. stopping updating cells reference in excel formulas when I insert a row
    By cristix5 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2008, 11:43 PM
  7. Insert a formula with relative cell reference based on a number in a column
    By Dcritelli in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-16-2007, 05:54 PM

Tags for this Thread

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