Sort/Filter Not Dynamic

[cookeetree]

G'day Excel Gods,

I have a worksheet that references my "Sample & Test Log". On entering a Request Number into cell $J$1, there is a formula that pulls the correct Concrete Sample Numbers for that request and inserts them into the worksheet. The worksheet is then printed for test data to be written on.

My full formula is:

=IFERROR(SORT(FILTER('[Sample & Test Log.xlsm]Concrete'!$C2:$C175,('[Sample & Test Log.xlsm]Concrete'!$A2:$A175=$J$1)*('[Sample & Test Log.xlsm]Concrete'!$V2:$V175=($AA$7-$AA$6)))),"")

This Part... '[Sample & Test Log.xlsm]Concrete'!$C2:$C175,('[Sample & Test Log.xlsm]Concrete'!$A2:$A175=$J$1 ...matches the Request Number.

This Part... '[Sample & Test Log.xlsm]Concrete'!$V2:$V175=($AA$7-$AA$6) ...matches the Age of the Sample (some are tested at 7 days, some at 28 days).

What I've discovered is that the references to the Concrete worksheet aren't dynamic; when new rows are added to the Log, the line references don't update to include the new row. If I insert a new row within the existing ones, there's no issue, it's only when I insert a new row above the first one that the problem occurs.

Is there a way around this???

Any assistance you could provide would be greatly appreciated,

Cheers, Jason.

 

[Jeffrey Mahoney]

This simple formula used in a named range can give you want you want. Replace the column reference with whatever column your lookup column is in
=OFFSET($B$1,1,0,MAX(($B:$B<>"")*(ROW($B:$B))-ROW($B$1)),1)

 

[Jeffrey Mahoney]

This assumes you have a header in B1. If you insert a row below the header it will still work. If you add data below it will still work.