The LET function is really useful for making formula readable and easy to track, but one thing is bugging me about it that I'm unable to figure out. My situation currently is that I'm trying to construct a bunch of user lists using a lookup, and concatenate these together for an easy comma separated list. I have a formula that does this:
=IFERROR(
CONCAT(
LET(
// Helper range variables
usernames, Sheet1!$A$1:$A$267,
names, Sheet1!$B$1:$B$267,
emails, Sheet1!$C$1:$C$267,
boards, Sheet1!$D$1:$D$267,
access, Sheet1!$J$1:$J$267,
keep, Sheet1!$O$1:$O$267,
lastaccess, Sheet1!$P$1:$P$267,
board, L31,
date, DATE(2023, 3, 13),
// The conditional to test against for selection of users
conditional, (boards = board)*(access = "")*(lastaccess <= date)*(keep = "?"),
// The valid user list returned as a transposed array
calc, TRANSPOSE(FILTER(emails, conditional)),
// The returned array is then turned into a comma-separated list for easy copy-pasting
calc&", "),
),
"None")
What this formula does is take the full list of all users, filters it based on the conditional and returns a comma-separated list of users. The important part of the calc is this TRANSPOSE(FILTER(emails, conditional)), where by changing emails to names or usernames etc, we can return a comma-separated list of whatever part we want.
Now the minor issue is that these are separated by a grouping called board, and there are 17 of these "boards". This means that if we want to get the usernames instead of the emails, I have to change the formula in 17 locations (the way I currently have it set up visually prevents a simple formula drag, so to keep the current visuals I'd need to either change the array to be filtered manually, or copy and paste the formula 16 times).
What I would like to be able to do, is just have a field called something like "Field to show", and it could be a dropdown that lets the user select between the LET variable names e.g. emails, names etc, and that is just filled in in the filter. There is a way to do it indirectly, where I could just do INDIRECT("Sheet1!$A$1:$A$267") for example, however I was hoping the user could just select "emails", and the LET formula could parse this and use the LET variable emails. Is this something that would be possible?
I apologise for this being long, I just wanted to give all the context possible. I have a way of doing it where the user selects from a dropdown, that dropdown then populates a cell with the range as a string, and then INDIRECT(range) is used in the LET formula, but I was basically wanting to find out if we could cut out the middle-man cell and just input the variable directly into the formula. Thanks for any help!
An idea could be to have your dropdown or input field stored in K31 and use something like this:
=IFERROR(
CONCAT(
LET(
choice, INDEX(Sheet1!$A$1:$C$267,,XMATCH(K31,{"usernames","names","emails"})),
boards, Sheet1!$D$1:$D$267,
access, Sheet1!$J$1:$J$267,
keep, Sheet1!$O$1:$O$267,
lastaccess, Sheet1!$P$1:$P$267,
board, L31,
date, DATE(2023, 3, 13),
conditional, (boards = board)*(access = "")*(lastaccess <= date)*(keep = "?"),
ARRAYTOTEXT(FILTER(choice, conditional)))),
"None")
Where I figured you'd only be interested in the first 3 columns as output choices, they're next to eachother and a simple INDEX/MATCH could work.
I also used ARRAYTOTEXT for putting the commas in between data and avoiding a trailing comma behind the last data. (TEXTJOIN(", ",,FILTER(choice, conditional)) could also do the trick there).