Event Management and Attendee list help

HollywoodStu

We are hoping to use Smartsheets for event management, with a master sheet having columns such as 'Name, Role, Company' and additional columns being the Event name (drop down list with dates) and 'I would like to attend this event' (checkbox)

We would then create a form for each event, using the basic columns and then using whichever event column we needed with the drop down dates.

What I'd like to understand is this.

E.g. Joe Bloggs attends three separate events. In the master sheet, his name would appear in three separate rows, showing his selection cascading across event 1 (yes), event 2 (yes), event 3 (yes).

Is there a way We could amalgamate that row in another sheet, perhaps, that would have joe bloggs on one row with checkboxes showing the events he's put down to attend?

Any help would be most welcome!

Andrée Starå

Hi @HollywoodStu

I hope you're well and safe!

Yes, you can use cross-sheet formulas to collect all the attendees in one row each. I'd recommend using an INDEX/MATCH combination.

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

HollywoodStu

Hi Andrée,

Thanks so much for replying. It's great to hear that this is possible!

I've read a couple of articles on INDEX/MATCH but am confused about how to even start pulling a formula together to achieve it.

I've attached 3 x pictures:

1. shows the source sheet
2. is the log sheet where I'd like to take the 3 x entries of Joe Bloggs and Sally Field and have them on individual rows
3. is the references i've created in the log sheet (I didn't know what to do with these after I'd created them!)

Could you advise how to create something that would pull this information across?

Thank you in advance,

Stuart

Picture1.png

Picture2.png

Picture3.png

ro.fei

Hey Stuart, let's see if I can help you understand how to do this.

To set up, I'm assuming you'll have an Event Sheet for each event, & an Event Log to summarize all of the Event Sheets. I made an example using the names you provided, with 3 Event Sheets & 1 Event Log. I'll use this to demonstrate for you. Essentially, you'll want to use the name or email address to match up whether or not they're marked as attending in each Event Sheet. For demonstration purposes, I have Joe attending all three events, while Sally is only attending events 1 & 3, & I used the Name column to match. Snapshots of the sheets below:

In the Event Log, each column will have a different formula that looks at the name in the row, match it up with the name in the corresponding Event Sheet, & return the value from the Attending checkbox column. In the Event 1 column in the Event Log, my formula looks like this:

=INDEX({Event Sheet 1 Attending}, MATCH(Name@row, {Event Sheet 1 Name}))

The {} brackets indicate a reference to another sheet. The first sheet reference {Event Sheet 1 Attending} will look at the Attending column in Event Sheet 1, & will return the value from there. Then in the MATCH function, the Name@row will look at the name in that row of your Event Log, & the second sheet reference {Event Sheet 1 Name} will look at the Name column in your Event Sheet 1 & match it up there.

The other column formulas look very similar, they just reference the other Event Sheets.

The formula in the Event 2 column is =INDEX({Event Sheet 2 Attending}, MATCH(Name@row, {Event Sheet 2 Name})). Same build, but with references to a different sheet (Event Sheet 2). The same for Event 3, =INDEX({Event Sheet 3 Attending}, MATCH(Name@row, {Event Sheet 3 Name})).

------- More Context on References -------

Since you seem new to these functions, I'll provide some extra context on the references. When you're typing out your functions, you'll see a box appear with an option to reference another sheet.

When referencing the columns in the other sheet, make sure you select the entire column. This will ensure the formula will look for a match no matter how far down the list the name is. Make sure your reference is looking at the correct sheet too! It also helps to give the reference a descriptive name so you know what it's looking at.

Clicking inside the {} brackets will allow you to edit an existing reference. That's how you can view the Reference Another Sheet window shown above. Here you can change what the reference is looking at & update the name of the reference if needed.

I hope this helps! I tried to be really detailed, but let me know if you have any more questions regarding what I did here. Good luck! 😊

ro.fei

@HollywoodStu See my comment above--sorry I forgot to tag you when I first responded! Let me know if you have any questions about what I explained. Hope it helps 😊

HollywoodStu

Hi there,

Sorry for not responding sooner as I'd taken some leave.

Thank you for coming back to me on this and the detail you've provided is fantastic!

Rather than having individual sheets for events, we were planning on running off one master sheet, which would mean duplicate rows with names on.

Using your explanation above - is it easy to transfer this to be able to capture this from one sheet:

Joe Bloggs appearing on multiple lines and with different columns checked as he attends different events, into another sheet with just Joe Bloggs on one row showing all his events?

ro.fei

@HollywoodStu Hey, hope you enjoyed your leave!

My advice would be to use separate event sheets, this will make it much easier to track in the event log (& also allow you to check the status of individual events via their specific sheets).

However, if you guys are married to the idea of using a single master event sheet let me know & I'll see if I can help you figure out a formula. The issue is oftentimes it'll only look at the first line that returns a match, meaning subsequent rows that also match might be missed & the data in the event log might not be accurate. I'd have to look further into it to see if I could help you work around that, but I'm not sure it'd be worth setting it up that way.

Hope this helps! Let me know if you need any more help with this 😊

HollywoodStu

Hi @ro.fei !

I was hoping to ask a follow up question on the steps you'd outlined.

The company i'm putting this together for doesn't have a defined list of people that will be attending the training courses/webinars - so for the master sheet, how would this work?

Is there a workaround in this case?

I have created the event sheets for the individual events, i'm just not sure how to ladder that up into the master document for both current and future events?