Is there a way to recognize repeated data between two sheets?

lcain

I have set up several sheets within a workspace to track our companies invitations to bid/proposals.

The set up flows like this:

1. Invitations to bid are received and input into an "Invitations to Bid" sheet
2. Management decides which projects to bid or not. Rows for projects we will bid are moved to a "Current Bid List". Rows for projects we will not bid are moved to a "Not Bidding" list.
3. Estimators review the "Current Bid List" and provide proposals to appropriate jobs. If a proposal is sent, the row is moved to the "Previously Bid" list. If not, the row is moved to the "Not Bidding List".

I have attached a crude chart to show the relationships between these four sheets.

Sometimes jobs are bid several times before they are awarded.

My question being: Is there a way to identify "rebid" jobs from the "Previously Bid" List when inputting in the "Invitations to Bid" list?

For example, if the job name and location matches on both sheets - a checkbox column is checked?

Thanks in advance for any suggestions!

Paul Newcome

You would count how many times that combo appears in the other sheet and then nest that in an IF statement to say that if the count is greater than zero (meaning at least one other instance), check the box as a rebid.

=IF(COUNTIFS({Other Sheet Job Name Column}, @cell = [Job Name]@row, {Other Sheet Location Column}, @cell = Location@row)> 0, 1)

Paul Newcome

You would count how many times that combo appears in the other sheet and then nest that in an IF statement to say that if the count is greater than zero (meaning at least one other instance), check the box as a rebid.

=IF(COUNTIFS({Other Sheet Job Name Column}, @cell = [Job Name]@row, {Other Sheet Location Column}, @cell = Location@row)> 0, 1)

lcain

That worked perfectly! Thank you very much!

Paul Newcome

Happy to help. 👍️