Forum Discussion
Name Manager: Let and col formulas not encompassing all data
kittenmeants If you're expecting the results to include rows 5 through 325, but it's only returning up to row 321, the most likely culprit is the formula definition for "Regions". COUNTA counts the number of cells in a range that are not empty. My guess would be that range E5:E325 contains 4 blank cells. TAKE(rng, COUNTA(rng)) will only return the number of rows that were counted as non-blank... if there are 4 blank cells somewhere within the data range, it will exclude the last 4 records.
As an alternative for the "Regions" definition, try the following:
=LET(rng, BeautyMatrix!$E$5:$E$350, first, INDEX(rng, 1), first:XLOOKUP(TRUE, rng<>"", rng, first,, -1))As for your second inquiry about returning 8 columns instead of 2, try using the SEQUENCE function:
=LET(col, XMATCH(E4, Names), CHOOSECOLS(Hours, col + SEQUENCE(, 8, 0)))It's difficult to grasp the full scope of what you're trying to accomplish without seeing all of your data. Hopefully this helps... Cheers!
kittenmeants If you're expecting the results to include rows 5 through 325, but it's only returning up to row 321, the most likely culprit is the formula definition for "Regions". COUNTA counts the number of cells in a range that are not empty. My guess would be that range E5:E325 contains 4 blank cells. TAKE(rng, COUNTA(rng)) will only return the number of rows that were counted as non-blank... if there are 4 blank cells somewhere within the data range, it will exclude the last 4 records.
As an alternative for the "Regions" definition, try the following:
=LET(rng, BeautyMatrix!$E$5:$E$350, first, INDEX(rng, 1), first:XLOOKUP(TRUE, rng<>"", rng, first,, -1))
As for your second inquiry about returning 8 columns instead of 2, try using the SEQUENCE function:
=LET(col, XMATCH(E4, Names), CHOOSECOLS(Hours, col + SEQUENCE(, 8, 0)))
It's difficult to grasp the full scope of what you're trying to accomplish without seeing all of your data. Hopefully this helps... Cheers!
- That worked perfectly. Thank you so much!
