Qualifying a contest entry - comparing items between two sheets

Hi I am new to Smartsheet and really wished I could just run a pivot table against a sales contest my team is running. But we are constantly getting new entries through a smartsheet form and I have created a smartsheet dashboard that updates in realtime as the entries come in.

I have two sheets:

One is the source form where each of the contest entries get populated with data around the sales deals. Let's call this {Entry Form}.

The other is a master roll up against the actual sales that were booked in the last month. There is a unique ID associated with the deal. {Master Sheet}

I want to create a column in the Entry Form that checks the box if it is a quailfied deal - meaning that the ID on the entry form matches the unique ID found on the master roll up.

=IF(COUNTIF({Master Sheet range }, [ID]@row) = 1, 1)

Trying to get it to return a 1 so I can check a box.

It keeps saying unparseable. So I think I did something wrong. Any assistance would be greatly appreciated. I'm highlighting the column in the Master Sheet that has all the ID's. So I am thinking it should work. But I guess I'm wrong.

Happy Friday


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Annie,

    I've just tested your formula, with a column named "ID", and it worked as expected! Since the [ID]@row is lighting up, indicating that there is a match there, I wonder if it could be your cross-sheet reference?

    Usually cross-sheet references will have a default range number put in place... in the formula you copied/pasted above there doesn't seem to be a range number. How did you retrieve the {Master Sheet range }?

    I would check this to make sure it's referencing the full column in your Master Sheet. If you click on that Range it should open a little window that allows you to select "Edit Reference". From here you can click the column title again in the source sheet to highlight the full column, and maybe re-name the reference if needed.

    If this hasn't helped, could you maybe post a screen capture of the reference window where you selected the Master Sheet Range column? (But block out any sensitive data).

    Hope this helps!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!