INDEX Function with Dynamic Array

[UnitedCloud01]

Hi Everyone

I have an array in my spreadsheet where I need to add rows periodically.

I have the following array with fixed parameters that I then update and have to copy down the column to the end of the new rows each time I add a series of new rows. I would like to have a more dynamic formula the negates this step. Currently the rows total 2100.

{=IFERROR(INDEX($C:$C,SMALL(IF($S$5:$S$2100<>"",IF($L$5:$L$2100="",IF($C$5:$C$2100<>"",ROW($S$5:$S$2100)))),ROWS(V$5:V5))),"")}

Column 'C' contains patients name that I add to a list. Column 'S' contains data that captures if this patient is overdue for a risk review. Column 'L' ensures the patient is open and not closed. Column 'V' then captures all patient names due for a risk review. A macro then pulls from this column and populates a cell where the patient names are all collated.

Many thanks for your assistance.

Chat soon

Scott

 

[UnitedCloud01]

Thanks for that
As you have 365 another option is

=FILTER(C5:C10000,(S5:S10000<>"")*(L5:L10000="")*(C5:C10000<>""))

This just goes in one cell & will spill down.

Thanks so much, Fluff. I did try this, but an error was returned #SPILL! I might just keep with the solution above for now.

 

[StephenCrump]

I did try this, but an error was returned #SPILL!

You only need this formula in one cell. The results of the FILTER will spill down the column, taking as many cells as is required to show the results.

The #SPILL! error just means that you have other values or formulae in the way. Get these out of the way and the formula should work?

 

[UnitedCloud01]

You only need this formula in one cell. The results of the FILTER will spill down the column, taking as many cells as is required to show the results.

The #SPILL! error just means that you have other values or formulae in the way. Get these out of the way and the formula should work?

Hi Stephen. Thanks so much. I didn't even think. The old formulas were remaining further down the column!! DOH.