Replace INDIRECT and ADDRESS with INDEX

[dejhantulip]

Hello community!

I need a little help with replacing an INDIRECT and ADDRESS functions combination with INDEX (which I believe would be a better suited and faster alternative... other alternatives are also welcomed!)

So I have the following:

Basically I am trying to fill up with an array of numbers Column C. The formula is basically grabbing the address of P71 (start of range I would like the array to fill up) and then using the MAX function to grab the last row number that is non-blank in Column U. This basically gives me the "range" I am trying to create for the array.

The result outputs a "1" times the row number in order to create an array of unique non-zero numbers whenever the "ACTIVIDAD:" text is found.

Thank you so much in advance for any help


ORLANDO

 

[jasonb75]

Maybe this in place of the entire INDIRECT section, P71:INDEX(U:U,MATCH(1e+100,U:U))

Not sure if that is what is needed but it's best guess from a screen capture, I'm not going to retype everything to test it.

 

[JamesCanale]

Not very sure of what you are looking for, but I got this:

Mr Excel Playground 3.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
71	71													ACTIVIDAD:
72	0													CANTIDAD:					x
73	0													UNIDAD:					x
74	0													MONEDA:					x
75	75													ACTIVIDAD:
76	0													CANTIDAD:
77	77													ACTIVIDAD:
78	0													CANTIDAD:					x
79	0													UNIDAD:
80	80													ACTIVIDAD:
81	81													ACTIVIDAD:					x
82	0													CANTIDAD:
83														UNIDAD:					x
84														ACTIVIDAD:
85														CANTIDAD:
86														UNIDAD:
Sheet18

Cell Formulas
Range	Formula
C71:C82	C71	=SEQUENCE(MAX(IF(ISBLANK(U:U),0,ROW(U:U)))-ROW(P71),1,ROW(P71))*IF(INDEX(P71:P999,SEQUENCE(MAX(IF(ISBLANK(U:U),0,ROW(U:U)))-ROW(P71)),1)="ACTIVIDAD:",1,0)
Dynamic array formulas.

 

[dejhantulip]

Not very sure of what you are looking for, but I got this:

Mr Excel Playground 3.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
71	71													ACTIVIDAD:
72	0													CANTIDAD:					x
73	0													UNIDAD:					x
74	0													MONEDA:					x
75	75													ACTIVIDAD:
76	0													CANTIDAD:
77	77													ACTIVIDAD:
78	0													CANTIDAD:					x
79	0													UNIDAD:
80	80													ACTIVIDAD:
81	81													ACTIVIDAD:					x
82	0													CANTIDAD:
83														UNIDAD:					x
84														ACTIVIDAD:
85														CANTIDAD:
86														UNIDAD:
Sheet18

Cell Formulas
Range	Formula
C71:C82	C71	=SEQUENCE(MAX(IF(ISBLANK(U:U),0,ROW(U:U)))-ROW(P71),1,ROW(P71))*IF(INDEX(P71:P999,SEQUENCE(MAX(IF(ISBLANK(U:U),0,ROW(U:U)))-ROW(P71)),1)="ACTIVIDAD:",1,0)
Dynamic array formulas.

Thank you very much!

 

[dejhantulip]

As per our conversation dear James, I am posting a little follow up... Thank you sir!

So now I am trying to achieve a multiplication of the dynamic array of E71# with each row that has a number in S column starting at S71 and I am making this resulting array as big as the array in C71# (which by the way has the same number of rows of E71#).

Thank you greatly for any help

 

[JamesCanale]

Not sure how you are calculating col E - but as long as the size of the array is consistent with C, then this aught to do. I also made a small change to Col C - I think it was missing the last row sometimes.

Mr Excel Playground 3.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
71	71		1	6										ACTIVIDAD:			6
72	0		1	0										CANTIDAD:					x
73	0		1	4										UNIDAD:			4		x
74	0		1	0										MONEDA:					x
75	75		1	0										ACTIVIDAD:
76	0		1	5										CANTIDAD:			5
77	77		1	5										ACTIVIDAD:			5
78	0		1	0										CANTIDAD:					x
79	0		1	10										UNIDAD:			10
80	80		1	10										ACTIVIDAD:			10
81	81		1	0										ACTIVIDAD:					x
82	0		1	0										CANTIDAD:
83	0		1	15										UNIDAD:			15		x
84														ACTIVIDAD:
85														CANTIDAD:			15
86														UNIDAD:
Sheet18

Cell Formulas
Range	Formula
C71:C83	C71	=SEQUENCE(1+MAX(IF(ISBLANK(U:U),0,ROW(U:U)))-ROW(P71),1,ROW(P71))*IF(INDEX(P71:P999,SEQUENCE(1+MAX(IF(ISBLANK(U:U),0,ROW(U:U)))-ROW(P71)),1)="ACTIVIDAD:",1,0)
E71:E83	E71	=C71#*0+1
F71:F83	F71	=INDEX(S71:S336,SEQUENCE(1+MAX(IF(ISBLANK(U:U),0,ROW(U:U)))-ROW(P71),1),1)*E71#
Dynamic array formulas.