Allowing an array (1 or zero) to read from a cell address

[Peter Davison]

I have this formula below which looks at 52 columns and I enter a 1 if I want it to show the column or a 0 if I don't
Is there any way instead of manually changing 1's to 0's I can get it to read the 1 or 0 from a cell address
I did try to replace a zero with a cell address (say D11) but it did not like that, also tried indirect and isnumber, also changed to 1 and 0 to True or False but nothing worked

=FILTER(Stores!$A$8:$AZ$4998,{1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})

Thank you for your help.

 

[Peter_SSs]

Would this shorter way also work for you?
Note that I have also removed also the absolute range/cell references as I don't think you should need those for this dynamic array formula

=LET(r,Stores!A7:AZ4998,b,INDEX(r,0,2),CHOOSECOLS(FILTER(r,(b=D2)+(b=Stores!B7)),FILTER(D5:D52,D5:D52<>"")))

 

[Peter Davison]

Thank you for further help.
Does that still allow a sort without Row 7 sorting with the rest?

 

[Peter Davison]

I've got this at the moment which includes the sort
=VSTACK(CHOOSECOLS(Stores!A7:AZ7,FILTER(D5:D56,D5:D56<>"")),SORT(CHOOSECOLS(FILTER(Stores!A8:AZ4998,Stores!B8:B4998=$D$2),FILTER(D5:D56,D5:D56<>"")),MATCH("Y",F9:AZ9,0)))

 

[Peter_SSs]

Does that still allow a sort without Row 7 sorting with the rest?

No, I had missed that bit about you wanting to sort. I think that you can shorten your formula a little though if you want. Try

=LET(r,Stores!A7:AZ4998,b,INDEX(r,0,2),d,CHOOSECOLS(FILTER(r,(b=D2)+(b=Stores!B7)),FILTER(D5:D52,D5:D52<>"")),VSTACK(TAKE(d,1),SORT(DROP(d,1),MATCH("Y",F9:AZ9,0))))

 

[Peter Davison]

Thank you for your help I will try that.