Conditional Formatting Two Conditions. Date and Contract Number

[CascadeDiver]

Good day

I have two tables. Table A has Contract Numbers. Table B has Contract Numbers, Dates, and Units Sold
I would like to have conditional formatting to highlight the Contract Numbers in Table A if an entry is made in Table B on today's date. This will give a visual indication if one of the Contracts in Table A has no entries in Table B on today's date.

I am having a difficult time coming up with the conditional formatting formula, possibly sumifs.

 

[CascadeDiver]

I forgot to specify the two tables are also on diffrenet sheets

 

[jasonb75]

I would say countifs rather than sumifs. As we don't know exactly how your sheet is laid out, I've used descriptions in place of ranges.

=COUNTIFS(Table B'AllContractNumbers,Table A!SingleContractNumber,Table B!AllDates,TODAY())

 

[CascadeDiver]

I would say countifs rather than sumifs. As we don't know exactly how your sheet is laid out, I've used descriptions in place of ranges.

=COUNTIFS(Table B'AllContractNumbers,Table A!SingleContractNumber,Table B!AllDates,TODAY())

I plugged in the table and column names but get an error problem with the formula

=COUNTIFS(Harvest_Data_Tbl[CONTRACT],Quoat_Contract_Tbl[CONTRACT],Harvest_Data_Tbl[DATE],TODAY())

I tried this as a regular formula and replaced the second criteria with a specific cell in the column range and the formula works fine though

 

[CascadeDiver]

I replaced named ranges with cells and got it working

Thank you for your help

=COUNTIFS('Harvest Log'!$A$9:$A$157,$B4,'Harvest Log'!$B$9:$B$157,TODAY())

 

[CascadeDiver]

Almost there. Unfortunate when I add an entry to the database the formatting range 'Harvest Log'!$A$9:$A$157 shifts down one row

 

[jasonb75]

You can't use table names / named ranges as the range for conditional formatting (as it looks like you've already discovered).

If you're inserting new rows at the top of the table, then I think that including the header row in the CF range should work.

I'm not sure if it will expand with new rows added to the bottom though, conditional formatting doesn't seem to like dynamic ranges of any kind.

 

[CascadeDiver]

You can't use table names / named ranges as the range for conditional formatting (as it looks like you've already discovered).

If you're inserting new rows at the top of the table, then I think that including the header row in the CF range should work.

I'm not sure if it will expand with new rows added to the bottom though, conditional formatting doesn't seem to like dynamic ranges of any kind.

You are correct, I am adding data to the top of the table. I changed the column ranges to use the entire column, that seems to do the trick. I'll try the Header Row if this doesn't pan out.

Again, thank you for your help

 

[jasonb75]

Conditional formatting triggers something known as volatile calculation. Using the entire column, you might notice some delay whenever you make changes to the sheet.