If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Using Index with nested small, not updating when I insert row of data on the reference page

Thread starter dpnyc
Start date Mar 18, 2015
Tags

iferror index range small()

dpnyc

New Member

Mar 18, 2015

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)

Thanks!