Surfacing Text from one sheet to another
Is there a way to surface text from one sheet to another if a checkbox is checked on the source sheet?
I have a sheet with a list of meeting participants and checkbox columns for each of the meetings associated with the program. Some people are in all of the meetings, and some are only in 1 or 2. I'd like to know if there is a way to pull all of the names for a specific meeting to an agenda sheet for that meeting. So if the box on the source attendees sheet is checked, then pull that name over to the specific meeting agenda sheet. I don't want all the names coming over to the destination sheet, only the ones for that specific meeting.
The source sheet contains columns that are check boxes (Meeting A, Meeting B, etc.) and an attendee column with everyone's names (Invitee). I'm looking for a way to pull the names of everyone who has a check in the box of the Meeting A column over to the Meeting A Agenda sheet.
Thanks!
Laurie
Best Answers
-
@Laurie A There should be 2 formulas. in the first cell should be the formula you got correct.
=INDEX(COLLECT({Name}, {AGM Meeting Invitees}, @cell = 1), 1)
In the cell directly below this formula you use the second formula (assuming the invitee column is the column that the formula is in)
=INDEX(COLLECT({Name}, {AGM Meeting Invitees}, @cell = 1), COUNT([Invitee]$1:[Invitee]1))
Then you can drag the formula down.
The absolute reference means the count will grow by 1 for every cell that you drag down, telling the index to grab the next name. If you want you can wrap this in an iferror for when it gets to the max as well so you can reserve spaces for extra names without showing an error in your sheet.
=IFERROR(INDEX(COLLECT({Name}, {AGM Meeting Invitees}, @cell = 1), COUNT([Invitee]$1:[Invitee]1)),"")
@Paul Newcome That sounds interesting. I've been using index for a while to avoid having to parse text, as I really don't like doing that, I always find text parsing to get more and more complicated the more you do it, and even when you get something working like it is supposed to someone manages to put in something you don't predict (particularly on sheets other people manage after I've created it)
It would be nice to have a shorter more reliable method of parsing.
Answers
-
You would need to use a JOIN/COLLECT to pull the list of names:
=JOIN(COLLECT({Other Sheet Attendees Column}, {Other Sheet Meeting A Checkbox Column}, 1), ",")
Then you would need to parse that listing out. I have a few different parsing solutions floating around. I will see if I can find any of them and get back to you.
-
HERE is a link to a published sheet I put together that shows a solution for parsing a list from another sheet down a single column (List Column in the sheet).
-
Thanks @Paul Newcome The JOIN(COLLECT works perfectly. You lost me on the parsing solution sheet though. I'm not clear as to how to implement that. If I'm understanding it right, I need to whittle the list down one name a time, is that correct?
-
@Laurie A I thought that's what you were trying to do, or were you just trying to collect the list into a single cell? I was under the impression that you were wanting to have each name listed in its own cell.
-
Instead of parsing you can index. It is much easier, just use the collect as the range for the index, and use a count function encompassing the previously posted names for the row reference and you don't need to worry about text parsing. (if you are trying to spread out the list of names over your sheet)
*this is if your sheet is only a few hundred rows max. Bigger than that and you'll start stressing smartsheet, as Paul's solution is much faster albeit more complex
-
@L@123 I actually like that idea (for smaller lists). I hadn't thought of doing it that way before. Large lists would put a bit of a strain because of the cross sheet reference being used so many times on top of everything else whereas my previous solution only has the one cross sheet formula.
But I am definitely going to have to play around with your idea as well.
-
@L@123 I tried using the formula below and got an unparseable error. AGM Meeting Invitees is the checkbox column indicating who is an invitee to that meeting, and Name is the column that contains all the names. What am I doing wrong?
=INDEX(COLLECT({AGM Meeting Invitees}, 1){Name}
-
@Paul Newcome I use this solution quite a bit actually, and yes I agree, I've broken sheets so badly with this that I had to delete them, they were unopenable. That said it did take 100+full column references between 26 sheets, all with thousands of rows, and even after making it a join statement instead of an index I had to break it out into 4 separate sheets and use a report to pull them together. As long as the sheet size isn't massive it doesn't seem to have any performance issues.
I haven't tested this out since the sheet size/performance update however, I would think it would be less affected now.
-
@Paul Newcome I am trying to get the names listed out one name to a row. I only meant I wasn't quite sure how to implement the formulas on the linked sheet you shared.
Upon reading some of your other posts on parsing I think I need to build on the formula in each additional cell. I haven't tried that yet (very complex), but I was confused looking at the rows containing all the numbers only being reduced by one at a time thinking my sheet would contain a multitude of rows with more than one name as shown on your sheet. Which is not what I was looking for.
Eg.:
Peter, Paul, Mary
Peter, Paul
Peter
I'm looking to have a single column with one name per row. Eg:
Peter
Paul
Mary
-
=INDEX(COLLECT({AGM Meeting Invitees}, 1){Name}
Your format for the collect statement is wrong. the syntax is
collect(range to return, range for criteria, criteria)
so your formula would look like
=index(collect({Name Column},{Meeting 1 Column},@cell = 1),1)
for the first result
After that you can use
=index(collect({Name Column},{Meeting 1 Column},@cell = 1),count([cell above]$1:[cell above]1))
and drag that down as far as you want.
-
Ah. Ok. Yeah. My list from the other sheet is a series of numbers. That's why it looks that way. When it parses out, the List column is where your Names would appear. The other two columns can be hidden to help keep the sheet looking clean.
I have replaced the numbers from the other sheet with names. Does that help clear things up?
The biggest change you will need to make is to the formula in String1. You would want to use the formula we worked out earlier in this thread instead of the one listed in the sheet.
To use the published sheet as a reference, you would create your three columns to replicate what is on the right side of the sheet. Then you would use the columns on the left to see which formulas go where along with some notes such as manual entry or drag-filling.
-
@L@123 This would be so much easier with a PARSE function. You may be interested in THIS DISCUSSION where some of us started talking about different ideas for a PARSE function. I can't speak for the others, but I know I have already put in an Enhancement Request. I'm just hoping it actually comes to fruition. Haha 🤞
-
@L@123 I haven't used any of these formulas before so I really appreciate your help.
I got it to work to pull over the first name, but I'm having trouble with the pull down. When you say [cell above], do you am I wanting to referencing the cell above including the row number? In the sample it has an absolute 1 ($1) so I'm not sure if that's supposed to be row # 1 or not.
My syntax is below and I'm getting a BLOCKED error. Name is the name column reference on the other sheet, AGM Meeting Invitees is the checkbox column on the other sheet, Invitee is the name of the column on the destination sheet where I want the list to populate.
=INDEX(COLLECT({Name}, {AGM Meeting Invitees}, @cell = 1), COUNT([Invitee]$1:[Invitee]1))
-
@Laurie A That particular formula would actually go in Row 2 and then dragfill down, but I would actually make a slight adjustment.
=INDEX(COLLECT({Name}, {AGM Meeting Invitees}, @cell = 1), COUNT([Invitee]$1:[Invitee]1) + 1)
@L@123 Correct me if I am wrong, but wouldn't we want to add 1 to the count so that the first entry isn't duplicated?
-
@Laurie A There should be 2 formulas. in the first cell should be the formula you got correct.
=INDEX(COLLECT({Name}, {AGM Meeting Invitees}, @cell = 1), 1)
In the cell directly below this formula you use the second formula (assuming the invitee column is the column that the formula is in)
=INDEX(COLLECT({Name}, {AGM Meeting Invitees}, @cell = 1), COUNT([Invitee]$1:[Invitee]1))
Then you can drag the formula down.
The absolute reference means the count will grow by 1 for every cell that you drag down, telling the index to grab the next name. If you want you can wrap this in an iferror for when it gets to the max as well so you can reserve spaces for extra names without showing an error in your sheet.
=IFERROR(INDEX(COLLECT({Name}, {AGM Meeting Invitees}, @cell = 1), COUNT([Invitee]$1:[Invitee]1)),"")
@Paul Newcome That sounds interesting. I've been using index for a while to avoid having to parse text, as I really don't like doing that, I always find text parsing to get more and more complicated the more you do it, and even when you get something working like it is supposed to someone manages to put in something you don't predict (particularly on sheets other people manage after I've created it)
It would be nice to have a shorter more reliable method of parsing.
Help Article Resources
Categories
Check out the Formula Handbook template!
