IF auto generated number in Row matches anywhere in 2 sheet columns then checkbox checked

Options
Wes S
Wes S
edited 12/09/19 in Formulas and Functions

Hello, 

I am trying to input a formula that automatically checks a checkbox when an auto generated number from a specific row in one sheet, is found anywhere in a column from 2 separate sheets. 

I've tried to make it work by referencing just one of the two sheets but it is behaving strangely. 

 

The formula I tried is =IF(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0), 1, 0)

If I drag the formula down the checkbox rows and populate the referenced sheet with matching Lead ID's I get a checked box on my first row and INVALID DATA TYPE on any others that should match. 

Thanks in advance for any help you're able to give.

Tags:
«1

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    The MATCH function actually produces a number based on where within a grid (range) the data is found. Your current formula is basically

     

    =IF(##, 1, 0)

     

    You don't really have a logical statement. What you would ideally have would be along the lines of

     

    =IF(## > 0, 1, 0)

     

    When we replace ## with your MATCH formula it is basically saying that if the data is found in any position within the grid (range), check the box.

     

    So here would be a minor adjustment to your current formula that should work. Let me know if it does.

     

    =IF(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0) > 0, 1, 0)

  • Wes S
    Options

    Hi Paul, 

     

    That worked! If it isn't a match it returns #NOMATCH rather than leaving the checkbox unchecked. I can live with that but is there a way to preserve the unchecked checkbox?

    Also, fairly new to formulas and wondering how I then add the other sheet to the MATCH range. 

    Thank you so much. 

    Wes

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    =IF(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0) > 0, 1, 0)

     

    To avoid the #NO MATCH error, we wrap the MATCH function in an IFERROR statement to get it to return the number 0. Since that is not greater than 0, it will leave the box unchecked.

    =IF(IFERROR(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0), 0) > 0, 1, 0)

    .

    To account for a second sheet, we would simply use an OR statement and duplicate the MATCH function with the exception of the range (we will wrap that one in an IFERROR as well). You will need to follow the appropriate cross sheet referencing steps for the second sheet range.

     

    =IF(OR(IFERROR(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0), 0) > 0, IFERROR(MATCH([Lead ID]1, {Second Sheet Range 1}, 0), 0) > 0), 1, 0)

     

    How does this work out for you?

  • Wes S
    Options

    Paul, you're amazing. Thank you very much for such a thorough response. It works like a charm and I understand how it's all functioning. 

    Is it just me or is this stuff a tonne of fun? 

    Thanks again, 

    Wes

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Happy to help! yes

     

    Is this stuff fun...? Give it time. Hahahaha

  • Wes S
    Options

    I have one more addition that I'm trying to make to this formula. I'm trying to also add- IF column = "Declined" and a date column has any date in it = checkbox checked. 

    This is my current attempt. 

    =IF(OR(IFERROR(MATCH([Lead ID]2, {Construction/Enhancements Estimate Workshe Range 1}, 0), 0) > 0, IFERROR(MATCH([Lead ID]2, {Maintenance Estimate Worksheet Range 1}, 0), 0) > 0), 1, IF(AND(IFERROR(MATCH("Declined", [Dave approval]2:[Wes Shelley approval]2, 0), 0), [Connected with on:]2 > 0), 1, 0))

    Any help is much appreciated.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    So you want the previous solution's criteria OR the word "Decline" in x-range OR a date in y-range?

     

    It could be any one of those with equal importance meaning it won't necessarily look in a specific order, it just needs to find any one of those things?

  • Wes S
    Options

    It would be the previous solutions criteria OR the word "Declined" in x range AND a date in y range.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Sorry. I just want to make sure we get this right...

     

    Previous Solution

     

    OR

     

    Date and "Declined" together

    .

    Correct?

  • Wes S
    Options

    Yes that's correct!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Perfect. Now for (hopefully) the last question...

     

    Date and Declined... Are they on the same sheet as the formula, or are they going to be a cross sheet reference?

     

    If x-sheet reference... Is there a way to uniquely identify the corresponding row (lead id/row id/etc)?

  • Wes S
    Options

    Date and Declined both exist on the same sheet as the formula on the row that the formula is also on. 

    Thanks so much Paul.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Phew. This makes things a lot easier. Hahaha. Give this a whirl...

     

    =IF(OR(OR(IFERROR(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0), 0) > 0, IFERROR(MATCH([Lead ID]1, {Second Sheet Range 1}, 0), 0) > 0), AND(ISDATE([Date Column]@row), [Other Column Name]@row = "Declined")), 1)

    .

    Basically what we are doing is

     

    =IF(OR(Previous Solution's Criteria, AND(Date, "Declined")), check the box)

  • Wes S
    Options

    It seems I'm getting an invalid operation error. 

     

    =IF(OR(IFERROR(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0), 0) > 0, IFERROR(MATCH([Lead ID]1, {Maintenance Estimate Worksheet Range 1}, 0), 0) > 0, AND(ISDATE([Connected with on:]@row), [Dave approval]1:[Wes Shelley approval]@row = "Declined")), 1)

  • Wes S
    Options

    I've tracked it down to the problem being [Dave approval]@row:[Wes Shelley approval]@row = "Declined" portion. 

     

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!