Forum Discussion
Let statement display multiple results in separate columns
Hello, Thanks to any one who can help. I Have a Let formula that pulls data from 3 separate sheets and stacks it in one column on a separate sheet. I also want to add another variable to the let for...
For example:
=LET(
List,CHOOSE({1,2,3,4,5,6,7},EducationData!B72:B239,EducationData!C72:C239,EducationData!E72:E239,EducationData!F72:F239,EducationData!G72:G239,EducationData!H72:H239,EducationData!J72:J239),
ListEmp,SORT(IF(ISBLANK(List),"Empty",List),1,1),
ListCol,INDEX(ListEmp,,3),
ListCol)
Thanks everyone. I was able to get it to pull and display correctly the following is the final Let Statement.
=LET(
List,SORT(IF(ISBLANK(EducationData!B72:B239),"Empty",EducationData!B72:J239),1,1),
List2,SORT(IF(ISBLANK(AdminData!B4:B35),"Empty",AdminData!B4:L35),1,1),
List3,SORT(IF(ISBLANK(TherapiesData!C87:C291),"Empty",TherapiesData!C87:M291),1,1),
Fltr,FILTER(List,INDEX(List,0,1)<>"Empty"),
Fltr2,FILTER(List2,INDEX(List2,0,1)<>"Empty"),
Fltr3,FILTER(List3,INDEX(List3,0,1)<>"Empty"),
FltrCol,CHOOSE({1,2,3,4,5,6,7},INDEX(Fltr,,1),INDEX(Fltr,,2),INDEX(Fltr,,5),INDEX(Fltr,,4),INDEX(Fltr,,6),INDEX(Fltr,,7),INDEX(Fltr,,8)),
FltrCol2,CHOOSE({1,2,3,4,5,6,7},INDEX(Fltr2,,1),INDEX(Fltr2,,2),INDEX(Fltr2,,5),INDEX(Fltr2,,10),INDEX(Fltr2,,7),INDEX(Fltr2,,8),INDEX(Fltr2,,11)),
FltrCol3,CHOOSE({1,2,3,4,5,6,7},INDEX(Fltr3,,1),INDEX(Fltr3,,2),INDEX(Fltr3,,5),INDEX(Fltr3,,3),INDEX(Fltr3,,7),INDEX(Fltr3,,8),INDEX(Fltr3,,10)),
NumCols, COLUMNS(FltrCol),
RowsA, ROWS(FltrCol),
RowsB, ROWS(FltrCol2),
RowsC, ROWS(FltrCol3),
SeqCol, SEQUENCE(RowsA+ RowsB+ RowsC),
SeqRow, SEQUENCE(1, NumCols),
Result, IF(SeqCol <= RowsA, INDEX(FltrCol,SeqCol, SeqRow),IF(SeqCol <= (RowsA + RowsB),INDEX(FltrCol2,SeqCol - RowsA, SeqRow),INDEX(FltrCol3,SeqCol-(RowsA + RowsB),SeqRow))),
Result
)