Forum Discussion
Name Manager: Let and col formulas not encompassing all data
Hello!
I have the following formulas in my name manager, that are attached to this formula in G5:
=LET(col, XMATCH(E4,Names), CHOOSECOLS(Hours, col, col + 1))
I'm not sure what I am doing wrong, but it only will transpose the data from row G5:G321 and H5:H321. I need the data to pull all the info to G and H 325.
(It would be perfect if it would pull in all the data from column G to column N as well) 🙂
Thank you!
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!
2 Replies
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!
