Qualifying a contest entry - comparing items between two sheets

Annie Murphy

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.

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!

Cheers,

Genevieve