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

 

[jasonb75]

Indirect is best avoided, try this instead.

A16

=MATCH("zzz",'90DATA09'!$W$:$W)

AE2 (Ctrl Shift Enter not required).

=INDEX('90DATA09'!$W$:$W, AGGREGATE(15,6,ROW('90DATA09'!$W$2:INDEX('90DATA09'!$W:$W,$A$16))/($AA$1='90DATA09'!$B$2:INDEX('90DATA09'!$B:$B,$A$16)), ROWS($AE$2:$AE2)))

 

[Keebler]

Indirect is best avoided, try this instead.

A16

=MATCH("zzz",'90DATA09'!$W$:$W)

AE2 (Ctrl Shift Enter not required).

=INDEX('90DATA09'!$W$:$W, AGGREGATE(15,6,ROW('90DATA09'!$W$2:INDEX('90DATA09'!$W:$W,$A$16))/($AA$1='90DATA09'!$B$2:INDEX('90DATA09'!$B:$B,$A$16)), ROWS($AE$2:$AE2)))

will that produce the list I need? I will try it

 

[Keebler]

Indirect is best avoided, try this instead.

A16

=MATCH("zzz",'90DATA09'!$W$:$W)

AE2 (Ctrl Shift Enter not required).

=INDEX('90DATA09'!$W$:$W, AGGREGATE(15,6,ROW('90DATA09'!$W$2:INDEX('90DATA09'!$W:$W,$A$16))/($AA$1='90DATA09'!$B$2:INDEX('90DATA09'!$B:$B,$A$16)), ROWS($AE$2:$AE2)))

the formula produces several circular errors

 

[Keebler]

will that produce the list I need? I will try it

Indirect is best avoided, try this instead.

A16

=MATCH("zzz",'90DATA09'!$W$:$W)

AE2 (Ctrl Shift Enter not required).

=INDEX('90DATA09'!$W$:$W, AGGREGATE(15,6,ROW('90DATA09'!$W$2:INDEX('90DATA09'!$W:$W,$A$16))/($AA$1='90DATA09'!$B$2:INDEX('90DATA09'!$B:$B,$A$16)), ROWS($AE$2:$AE2)))

 

[Keebler]

I removed the $W$ <second $> but still did not work.
the only way ive gotten any usable results previously is using indirect (granted, its not the best choice, but it has worked) it is not working in this case, which is why I posted my query

 

[jasonb75]

I removed the $W$ <second $> but still did not work.

That was a typo but it should work as expected when corrected. 'did not work' is not a sufficient description for me to correct any additional errors.

 

[Keebler]

That was a typo but it should work as expected when corrected. 'did not work' is not a sufficient description for me to correct any additional errors.

the did not work was in response to the " =MATCH("zzz",'90DATA09'!$W$:$W) " formula.
the result was 605.. i have ZERO idea where that came from, I can not find a 605 anywhere in any of my spreadsheets.... also, when I adjusted the "zzz" to the search variable I wanted, I got an "#N/A"
does that help?

 

[jasonb75]

when I adjusted the "zzz" to the search variable I wanted, I got an "#N/A"

Don't adjust anything, that is why it is not working. The formula that you say does not work is working as it should if it returns 605. It is meant to return the number of the last row with data in the sheet. The search variable is in the second formula, the same as it was in your original fixed range formula.

The second formula does the same as your original formula with the exception that it uses the row number returned by the first formula to make the range dynamic as you have asked.

 

[Keebler]

Don't adjust anything, that is why it is not working. The formula that you say does not work is working as it should if it returns 605. It is meant to return the number of the last row with data in the sheet. The search variable is in the second formula, the same as it was in your original fixed range formula.

The second formula does the same as your original formula with the exception that it uses the row number returned by the first formula to make the range dynamic as you have asked.

thank you for the clarification, however, there is NO data currently after line 480, so, I still am at a loss as to why the fist formula is returning 605