IF auto generated number in Row matches anywhere in 2 sheet columns then checkbox checked
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.
Comments

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)

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

=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?

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

Happy to help!
Is this stuff fun...? Give it time. Hahahaha

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.

So you want the previous solution's criteria OR the word "Decline" in xrange OR a date in yrange?
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?

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

Sorry. I just want to make sure we get this right...
Previous Solution
OR
Date and "Declined" together
.
Correct?

Yes that's correct!

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 xsheet reference... Is there a way to uniquely identify the corresponding row (lead id/row id/etc)?

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.

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)

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)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!