Filter error in results based on cell reference

[ianjwerner]

I can't figure out how to write a formula so that I don't have to update the formula every time more information is added to the sheet.
=IF(C3=0,UNIQUE(FILTER(SURVEYS!A3#,(SURVEYS!G3#>=SOURCE!E3)*(SURVEYS!G3#<=SOURCE!F3),"No Result")),SORT(FILTER(SURVEYS!A3#,(SURVEYS!B3:B886=CARD!C3)*(SURVEYS!G3#>=SOURCE!E3)*(SURVEYS!G3#<=SOURCE!F3),"NO RESULTS")))

If I add more information to my sheet I have to update B886 to B(whatever line the data goes to) less or more...

I would like to just have it Surveys!B3# like the rest of the formula so it catches whatever is in and below B3# then the formula results in "No Results" and only way data is displayed is if C3=0

Help!

 

[Fluff]

Try

index(SURVEYS!B:B,sequence(rows(g3#),,3)=CARD!C3

 

[ianjwerner]

Try

index(SURVEYS!B:B,sequence(rows(g3#),,3)=CARD!C3

I'm not following. Do I use your suggested formula in place of my formula? I have the rest of the formula referencing date ranges.

Or do I input your suggestion only in a specific point?

IF(C3=0,UNIQUE(FILTER(SURVEYS!A3#,(SURVEYS!G3#>=SOURCE!E3)*(SURVEYS!G3#<=SOURCE!F3),"No Result")),SORT(FILTER(SURVEYS!A3#,(index(SURVEYS!B:B,sequence(rows(SURVEYS!G3#),,3)=CARD!C3)*(SURVEYS!G3#>=SOURCE!E3)*(SURVEYS!G3#<=SOURCE!F3),"NO RESULTS")))

 

[Fluff]

You have done that correctly, but I missed a closing bracket, it should be

IF(C3=0,UNIQUE(FILTER(SURVEYS!A3#,(SURVEYS!G3#>=SOURCE!E3)*(SURVEYS!G3#<=SOURCE!F3),"No Result")),SORT(FILTER(SURVEYS!A3#,(index(SURVEYS!B:B,sequence(rows(SURVEYS!G3#),,3))=CARD!C3)*(SURVEYS!G3#>=SOURCE!E3)*(SURVEYS!G3#<=SOURCE!F3),"NO RESULTS")))

 

[ianjwerner]

That works to populate all my results still, but it doesn't populate results if I change to a specific associate's name or change my date range.

 

[Fluff]

Did your formula work correctly before?

 

[ianjwerner]

I guess from the one I had provided you in my first question it did not. .... It worked when all of these Cell Reference# were specific to G3:G886
When I switched them to have the # sign after the values the date filter stopped working.

=IF(C3=0,UNIQUE(FILTER(SURVEYS!A3#,(SURVEYS!G3:G886>=SOURCE!E3)*(SURVEYS!G3:G886<=SOURCE!F3),"No Result")),SORT(FILTER(SURVEYS!A3#,(SURVEYS!B3:B886=CARD!C3)*(SURVEYS!G3:G886>=SOURCE!E3)*(SURVEYS!G3:G886<=SOURCE!F3),"NO RESULTS")))

so ideally the functionality of the formula where it has specific limits (G3:G886) and (B3:B886) > but with a formula so I don't have to update the formula everytime I add or remove data from the source file would be perfect.

 

[Fluff]

Is G3:G886 on the surveys sheet a spilled range or just a normal range.

 

[ianjwerner]

Is G3:G886 on the surveys sheet a spilled range or just a normal range.

It is a value lookup.
but it's not a spill formula.

 

[Fluff]

In that case you need to specify the actual range. The # in something like A2# signifies that it is a spill range.