Results 1 to 3 of 3

Thread: Index, Small - Not updating when insert rows

  1. #1

    Index, Small - Not updating when insert rows



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I have this code that looks to my sheet with Cash Flow Transactions, and returns values associated with the date range (O7 = beginning date), if not it returns the period end date (P3). When I insert a row in my Cash Flow Transactions Page the formula does not update for the new row (instead it skips a row). Does anyone have any ideas on how I can fix it? The problem part of the function is:ROW('Cash Flow Transactions'!A1)),COLUMN(('Cash Flow Transactions'!A1))) which is what is skipping when I insert, and returning a ref# if I delete.

    =IFERROR(INDEX('Cash Flow Transactions'!$A$4:$A$313,SMALL(IF(($O$7<='Cash Flow Transactions'!$A$4:$A$313)*($P$3>='Cash Flow Transactions'!$A$4:$A$313),MATCH(ROW('Cash Flow Transactions'!$A$4:$A$313),ROW('Cash Flow Transactions'!$A$4:$A$313))),ROW('Cash Flow Transactions'!A1)),COLUMN(('Cash Flow Transactions'!A1))),$P$3)

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Try removing the reference to the other sheet in that part of the formula... it isn't necessary as it is just a counter....

    Try:

    =IFERROR(INDEX('Cash Flow Transactions'!$A$4:$A$309,SMALL(IF(($O$7<='Cash Flow Transactions'!$A$4:$A$309)*($P$3>='Cash Flow Transactions'!$A$4:$A$309),MATCH(ROW('Cash Flow Transactions'!$A$4:$A$309),ROW('Cash Flow Transactions'!$A$4:$A$309))),ROW(A1)),COLUMN(A1)),$P$3)


  3. #3
    Works great, thanks!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •