Help marking off a guest list from a registration sheet

Options
alwayslearning
alwayslearning ✭✭✭✭
edited 03/24/21 in Formulas and Functions

We’re planning a large meeting. We’ll be using Smartsheet for registrations via a form. There are about 80 guests we want to make sure register. I have attached screenshots. Sheet A is the guest list. Sheet B will be collecting the registrations.  

Is there a way to set up a cross-sheet formula that marks off each person on the guest list when they register using the last name? On the guest list (Sheet A), I have a column called “Registered.” I’d like to see a checkbox, a green symbol, or the word “yes” if someone registers with the last name in that row. 

I struggle with formulas. Is there a way to do this?

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Oops. Try:

    =IFERROR(IF(ISTEXT(VLOOKUP([last name]@row, {spring officers' meeting registration 4-24 range 1}, 1, False)),"Yes", ""),"")

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @alwayslearning ,

    What you asked can be done but people with the same last name will be marked as registered if 1with that name registers. Is that ok?

    In the [Registered] column place this column formula:

    =IFERROR(VLOOKUP([last name]@row, {insert external range}, 1, False),"", "YES")

    The external range will be the last name column on your registration sheet.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • alwayslearning
    alwayslearning ✭✭✭✭
    Options

    Hi Mark,

    The response is #INCORRECT ARGUMENT SET. I have attached a screenshot of the formula. I'm sure I'm missing something like a comma or parentheses that I just don't see. Do you see my error?


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Oops. Try:

    =IFERROR(IF(ISTEXT(VLOOKUP([last name]@row, {spring officers' meeting registration 4-24 range 1}, 1, False)),"Yes", ""),"")

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • alwayslearning
    alwayslearning ✭✭✭✭
    Options

    You are a genius! It's working now! I can't thank you enough. I really struggle with formulas. And -- I'm not worried about the same last name issue. There are only two people on my list with the same last name. I will manually check those two. Thank you AGAIN!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!