Choose what cells are populated

[ste33uka]

Hi there are way to choose what cells are populated?
currently with this formula in A1 it would populate A1 TO A12
=IFERROR((LET(f,FILTER($AY$204296:$AY$525555,($AW$204296:$AW$525555=F5)*($AA$204296:$AA$525555=F6)),INDEX(f,SEQUENCE(MIN(12,ROWS(f)),,ROWS(f),-1)))),"")

Is there a way to populate A1 to A4 , C1 to C4 and E1 to E4 with just one formula ?
Thanks

 

[Anthony47]

I don't think you can do that, without "wasting" B1:B4 and D1:D4; I mean that you might fill A1:E4 but the formula would overwrite B1:B4 and D1:D4.
You can fill with a single formula A1:C4, i.e. splitting the 12 values in 3 contiguous columns; for example, in A1:

=IFERROR(LET(f,FILTER($AY$204296:$AY$525555,($AW$204296:$AW$525555=F5)*($AA$204296:$AA$525555=F6)),Work,INDEX(f,SEQUENCE(MIN(12,ROWS(f)),,ROWS(f),-1)),WRAPCOLS(Work,4)),"")

If you receive the #SPLILL! error it means that part of the area the formula needs to populate is not free from other contents.

To compile A1:A4 - C1:C4 - E1:E4, without affecting B1:B4 and D1:D4, you might insert the given formula in a free area of your workbook, for exmple in K1, then in A1, C1 and E1 insert the formulas

=K1:K4
=L1:L4
=M1:M4

 

[HighAndWilder]

Hi there are way to choose what cells are populated?
currently with this formula in A1 it would populate A1 TO A12
=IFERROR((LET(f,FILTER($AY$204296:$AY$525555,($AW$204296:$AW$525555=F5)*($AA$204296:$AA$525555=F6)),INDEX(f,SEQUENCE(MIN(12,ROWS(f)),,ROWS(f),-1)))),"")

Is there a way to populate A1 to A4 , C1 to C4 and E1 to E4 with just one formula ?
Thanks

Do you need the ranges B1:B4 and D1:D4 to be available for something else?

 

[HighAndWilder]

Hi there are way to choose what cells are populated?
currently with this formula in A1 it would populate A1 TO A12
=IFERROR((LET(f,FILTER($AY$204296:$AY$525555,($AW$204296:$AW$525555=F5)*($AA$204296:$AA$525555=F6)),INDEX(f,SEQUENCE(MIN(12,ROWS(f)),,ROWS(f),-1)))),"")

Is there a way to populate A1 to A4 , C1 to C4 and E1 to E4 with just one formula ?
Thanks

This is a bit clumsy but it 'appears' to do the job UNLESS you need B1:B4 and D1:D4 to be available for something else.

Range M1:M4 needs to be blank.

The Conditional Formatting hides the zeros with a white font color.

Having zeros in columns B and D may be a problem for you though.

Excel Functions.xlsm
	A	B	C	D	E	F
1	I	0	P	0	Y
2	U	0	S	0	P
3	G	0	L	0	Y
4	M	0	H	0	X
5						H
6						J
ChooseCells

Cell Formulas
Range	Formula
A1:E4	A1	=LET(a,VSTACK(LET(f,FILTER($AY$204296:$AY$525555,($AW$204296:$AW$525555=F5)*($AA$204296:$AA$525555=F6)),INDEX(f,SEQUENCE(MIN(12,ROWS(f)),,ROWS(f),-1))),M1:M4),WRAPCOLS(VSTACK(CHOOSEROWS(a,1,2,3,4),CHOOSEROWS(a,13,14,15,16),CHOOSEROWS(a,5,6,7,8),CHOOSEROWS(a,13,14,15,16),CHOOSEROWS(a,9,10,11,12)),4))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:E4	Cell Value	=0	text	NO

 

[ste33uka]

Thanks for all suggestions, dont think it is possible what i am looking for