Sign in to join the conversation:
How do I write a formula to check a box next to an ID Number if that same ID Number is also present in another column? So Column 1 is ID Number to complete and Column 2 is ID Number completed list. Thanks in advance!
You could try something along the lines of...
=IF(COUNTIFS([Column 2]:[Column 2], [Column 1]@row) > 0, 1)
This will count how many times the ID number in Column 1 in whatever row the formula is in shows up in Column 2. If it shows up at all, it will check the box.
Thank you Paul. I tried that and it's not checking boxes although I know I have matching numbers within the two columns.
Hi Summer,
Can you share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
Quick question... Are either Column1 or Column2 the Primary column? If not... Are the numbers for one column on the right and the numbers for the other column on the left? SS Sometimes stores what looks like numbers as actually a text string that just represents numbers. Text strings are automatically formatted to be left aligned while actual numbers are aligned to the right.
This may be the key as to why things aren't working because the formula I provided above SHOULD work.
@Paul Newcome You're formula worked, I've been looking at this for hours trying to figure it out. Such a simple formula even referencing a sheet range.
Thank you!
Melissa
@MCaputo Happy to help. 👍️
@Paul Newcome I have an interesting scenario I'm using this formula =IF(MATCH([Facilitator Name]@row, {first name}, 0), 1, 0) for a checkbox
The facilitator name is in sheet 1 and the first name column is in sheet 2
I want the checkbox to check if the names on sheet 1 & sheet 2 match with this formula. I received one row checked and the other rows now list #invalid data type even though names match on both sheet. Both Name columns are the primary column for each sheet so I wonder if that's the issue????
Not sure if this is the best formula for this situation either? I just need the checkbox column to check or remain unchecked if a name is not found on sheet 2.
@Stacey Carrasco The MATCH function outputs a numerical value to represent where within the range the match was found. It is not a true/false type of function. That means if the name is the 5th one down then your IF statement is essentially reading:
=IF(5, 1, 0)
You can use the MATCH function, but you would need to say if MATCH is greater than zero or greater than or equal to one which would then change the IF to
=IF(5 >= 1, 1, 0)
@Paul Newcome ok, interesting so I changed around the formula to indicate > = 1 but it's still not working because it's checking off some boxes when the statement is false and should return #no match
=IF(MATCH([Facilitator Name]@row, {first name})>=1, 1, 0)
🤔
@Stacey Carrasco Your new formula isn't specifying an exact match.
=IF(MATCH([Facilitator Name]@row, {first name}, 0)>=1, 1, 0)
@Paul Newcome that did the trick!!! 😂😁
thanks for seeing me through this one!!
@Stacey Carrasco Happy to help. 👍️
@Paul Newcome i'm using the same helpful formula as above but have my columns listed to be checked off monthly. Can i add another criteria to this formula? the month (or helper)?
=IFERROR(IF(MATCH([name]@row, {name}, 0) >= 1, 1, 0), " ")
I would need the column to be checked off when it matches name and month
@Paul Newcome Can you help explain why this formula, returns a #no match in the cell instead of leaving the check box blank?
=IF(MATCH([Email(s)]@row, {HS Reg emails}, 0) >= 1, 1, 0)
I know I have this formula someplace else but for some reason I can't seem to locate. 🤦♀️😁
@Stacey Carrasco If there is no match, it will output that error. It does not output zero. Try incorporating an IFERROR to output a zero in the event of a no match error.
=IF(IFERROR(MATCH(………), 0) >= 1, 1)
Hi! I'm fairly new to SmartSheets but have been trying a number of things that havent yet worked. I want to be able to show for each parent row, the number of child rows completed as a %. So for the parent row Process Overview there are 7 tasks and 6 tasks are complete so I want it to show as 90%. Can anyone help me?
Hello, I am trying to find the average audit score for a range of percentages between two dates. There are divider rows for organization by month. I have been trying to utilize the following formula but continue to get #DIVIDE BY ZERO for an answer: =AVG(COLLECT([Audit Score]:[Audit Score], [Date of Chat]:[Date of Chat],…
I'm officially stumped. I'm tracking Admissions Reps visits to schools, the types of visits and the amount of visits and inquiries. The columns are as follows: Created Date Rep Name Type of Event 1 - Dropdown: Tabletop, College Fair, Classroom Presentation, School Visit, School Tour District 1 - Dropdown with all Districts…
