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

 

[kvsrinivasamurthy]

2100 can be changed to 10000, expecting total rows does not exceed 10000.

 

[UnitedCloud01]

2100 can be changed to 10000, expecting total rows does not exceed 10000.

Oh wow, so it really is that simple. I did see some suggestions online but was doubting myself. Thanks so much. Just checking, that won't impact the processing speed, will it?

 

[StephenCrump]

Another possibility is to use an Excel table. Then your formulae will automatically adjust down the columns if you add new rows.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3
4			Name	2	3	4	5	6	7	8	9	Col L	11	12	13	14	15	16	Due?	18	19	Names due
5			A																x		1	A
6			B																x			B
7			C																			I
8			D									x							x
9			E
10			F
11			G									x							x
12			H
13			I																x
14
15
16
Sheet3

Cell Formulas
Range	Formula
U5	U5	=[@18]+1
V5:V15	V5	=IFERROR(INDEX([Name],SMALL(IF([Due?]<>"",IF([Col L]="",IF([Name]<>"",ROW([Name])-ROW(Table1[[#Headers],[Name]])))),ROWS(Table1[[#Headers],[Name]]:[@Name])-1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

But if you're using VBA to extract the names due for a review, you could bypass this calculation column entirely. Instead just use VBA to filter the table, and then your code can pick up the filtered names from column C.

 

[kvsrinivasamurthy]

Oh wow, so it really is that simple. I did see some suggestions online but was doubting myself. Thanks so much. Just checking, that won't impact the processing speed, will it?

No. Referring whole column like c:c or whole row like 2:2 will affect speed

 

[UnitedCloud01]

No. Referring whole column like c:c or whole row like 2:2 will affect speed

Thanks so much.

 

[UnitedCloud01]

Another possibility is to use an Excel table. Then your formulae will automatically adjust down the columns if you add new rows.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3
4			Name	2	3	4	5	6	7	8	9	Col L	11	12	13	14	15	16	Due?	18	19	Names due
5			A																x		1	A
6			B																x			B
7			C																			I
8			D									x							x
9			E
10			F
11			G									x							x
12			H
13			I																x
14
15
16
Sheet3

Cell Formulas
Range	Formula
U5	U5	=[@18]+1
V5:V15	V5	=IFERROR(INDEX([Name],SMALL(IF([Due?]<>"",IF([Col L]="",IF([Name]<>"",ROW([Name])-ROW(Table1[[#Headers],[Name]])))),ROWS(Table1[[#Headers],[Name]]:[@Name])-1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

But if you're using VBA to extract the names due for a review, you could bypass this calculation column entirely. Instead just use VBA to filter the table, and then your code can pick up the filtered names from column C.

Thanks so much for taking the time to do this, Stephen. I will have to look at what is involved in converting my sheet to a table. I currently have multiple clinicians in a busy clinic accessing it. I am self taught so not the greatest with using Excel to its potential!!

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[UnitedCloud01]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks so much, Fluff. I have just updated my profile.

 

[Fluff]

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.