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]

If you have the CHOOSECOLS function, you can just list the columns that you want (effectively eliminating the need for all the zeros). This also allows you to change the order of the columns too, should you so wish. For example, this would list columns A, E, C and J

=CHOOSECOLS(Stores!$A$8:$AZ$4998,1,5,3,10)

If you do not have CHOOSECOLS then you could achieve the same result with this.

=LET(r,Stores!$A$8:$AZ$4998,INDEX(r,SEQUENCE(ROWS(r)),{1,5,3,10}))

 

[Peter Davison]

I think I am nearly there, it is brilliant.
The problem I have got is I have a list of cells - D1 to D52 which relate to the 52 columns (Stores!$A$8:$AZ$4998)
I want to be able for the user to enter in a cell in the D column and then those selected are the only ones that are displayed.

I tried putting all the cells in the formula but unless there is a column number in all entries then it shows an error. So it becomes all or nothing unless the formula is changed each time.

CHOOSECOLS(Stores!$A$8:$AZ$4998,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31,D32,D33,D34,D35,D36,D37,D38,D39,D40,D41,D42,D43,D44,D45,D46,D47,D48,D49,D50,D51,D52)

Do you think there is a way of achieving this?

 

[Fluff]

How about

CHOOSECOLS(Stores!$A$8:$AZ$4998,filter(D5:D52,D5:D52<>""))

 

[Peter Davison]

Wow, perfect.
I was trying the filter but had it in the wrong place.
Thanks for all your help

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter Davison]

Sorry could I just ask one more thing on this subject I want to add a further filter, but just can't seem to get it in the right place
This is my current formula
=VSTACK(CHOOSECOLS(Stores!$A$7:$AZ$7,FILTER($D$5:$D$52,$D$5:$D$52<>"")),CHOOSECOLS(Stores!$A$8:$AZ$4998,FILTER($D$5:$D$52,$D$5:$D$52<>"")))
The VStack bit is so I can hold the column headings on a sort
I need a second filter based on the second part of the formula
CHOOSECOLS(Stores!$A$8:$AZ$4998,FILTER($D$5:$D$52,$D$5:$D$52<>""))

The filter I need to add to this is
filter(Stores!$A$8:$AZ$4998,Stores!$b$8:$b$4998=$D$2)
but I just can't seem to find the right place to make it work.
Sorry to be a pain.

 

[Fluff]

Try

=VSTACK(CHOOSECOLS(Stores!$A$7:$AZ$7,FILTER($D$5:$D$52,$D$5:$D$52<>"")),CHOOSECOLS(filter(Stores!$A$8:$AZ$4998,Stores!$b$8:$b$4998=$D$2),FILTER($D$5:$D$52,$D$5:$D$52<>"")))

 

[Peter Davison]

Again thank you so much
I just didn't have a bracket in the correct place

 

[Fluff]

You're welcome.