Dynamic Search formula

[Keebler]

so, I have a workbook that has one sheet that has thousands of rows of data that is constantly being updated, I have managed to create a formula/function that works when I enter it ({ctrl} {shft] {enter}) but as I mentioned, the data sheet is constantly being updated...
here is what I have thus far...

CHRISTMAS MOVIES DB2.xlsm
	AE
2	20 Years Of Christmas With The Tabernacle Chior
2021

Cell Formulas
Range	Formula
AE2	AE2	=INDEX('90DATA09'!$W$2:$W$463, SMALL(IF($AA$1='90DATA09'!$B$2:$B$463, ROW('90DATA09'!$B$2:$B$463)-ROW('90DATA09'!$B$2)+1), ROW(1:1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AE2:AE200	Expression	=AM3="missing"	text	NO
AE2:AE200	Expression	=BA3="MISSING"	text	NO

so, in this, please ignore the conditional formatting...
What I need to have is the ranges (90DATA09'!$W$2:$W$463 and '90DATA09'!$B$2:$B$463) be more flexible.. so that I can use like indirect to reference an evolving range *note 90DATA09 is the sheet with the data in this example
by evolving range I mean one that I have on another sheet that counts the number of rows (in this example) then creates an address like this [A16&":"&C16))] *note a16 is where I have the start of the range ($W$2) and c16 is where I have [ADDRESS($A$14+1,$B16))] *note A14 is where I have the count of rows, and B16 is the column. all this creates [

$w$2:$W$464

]
which I add the sheet name to to create my indirect reference... '90DATA09'!$w$2:$w$464

so here is the problem... whenver the 90DATA09 sheet updates, I have to change every row on ajacent pages to reflect the new range... is there a way to use indirect to pull the new addresses?

thank you

 

[Keebler]

so, here is what I did...

I used the formula =COUNTA(A2:A5007)+1 to get the total number of active rows, then i applied your second formula and got it to work (with a slight adjustment)

CHRISTMAS MOVIES DB2.xlsm
	AE
2	20 Years Of Christmas With The Tabernacle Chior
2021

Cell Formulas
Range	Formula
AE2	AE2	=INDEX('90DATA09'!$W:$W, AGGREGATE(15,6,ROW('90DATA09'!$W$2:INDEX('90DATA09'!$W:$W,$AD$1))/($AA$1='90DATA09'!$B$2:INDEX('90DATA09'!$B:$B,$AD$1)), ROWS($AE$2:$AE2)))

all I did was then change the cell reference to where I copied the count (from $a$16 to $ad$1) and it works
so, then I copied AD:AE to all the subsequent pages and it updates exactly like I was wanting

thank you for your time and help
Ken

I dont know how to mark this as solved, because of the changes,,, if you could re enter your formula with the changes I made, then I can mark YOUR formulas as what solved this

 

[Keebler]

so, here is what I did...

CHRISTMAS MOVIES DB2.xlsm
	AE
2	20 Years Of Christmas With The Tabernacle Chior
2021

Cell Formulas
Range	Formula
AE2	AE2	=INDEX('90DATA09'!$W:$W, AGGREGATE(15,6,ROW('90DATA09'!$W$2:INDEX('90DATA09'!$W:$W,$AD$1))/($AA$1='90DATA09'!$B$2:INDEX('90DATA09'!$B:$B,$AD$1)), ROWS($AE$2:$AE2)))

all I did was then change the cell reference to where I copied the count (from $a$16 to $ad$1) and it works
so, then I copied AD:AE to all the subsequent pages and it updates exactly like I was wanting

thank you for your time and help
Ken

I dont know how to mark this as solved, because of the changes,,, if you could re enter your formula with the changes I made, then I can mark YOUR formulas as what solved this

Im not sure why the count says 5007.. it says 2007
=COUNTA(A2:A2007) on page 90DATA09
then on page 2021 on cell AD1 I have
=(IF('90DATA09'!DE1="","",'90DATA09'!DE1+1))

 

[jasonb75]

With regard to the first formula returning a row farther down the sheet than the last data entry, if there are formulas in the sheet that return blanks then they will be counted as well.

I dont know how to mark this as solved, because of the changes

As you needed to edit the formula for it to work correctly you should mark your own reply with the formula that works as the solution.

 

[Keebler]

With regard to the first formula returning a row farther down the sheet than the last data entry, if there are formulas in the sheet that return blanks then they will be counted as well.

As you needed to edit the formula for it to work correctly you should mark your own reply with the formula that works as the solution.

Thank you, but I never would have got it without you pointing me in the correct direction...