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...
Yes I agree I can do that but I was hoping to have it spill with the other formula. So I can reference the spill data elsewhere if I do the xlookup and drag down in the second column and the original data get larger I will have to continue to drag down every time the data changes.
I might have found away. but i have hit a little snag. Does anyone know how to reference a column inside an array in a let statement?
=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),
ListEmp)
In the ListEmp Variable where it references the list variable. How do I specify a specific column instead of the whole array.
Thanks
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 )- Thanks That was very helpful. Quick question do you know if you can use the choose statement on array?
Not sure what you mean.
The syntax of CHOOSE is CHOOSE(index, value1, value2 ,...)
CHOOSE({1,2}, array1, array2) works, but CHOOSE({1,2}, array) doesn't.
