Conditional formatting two cells based on a checkbox

Balint79 

Thanks for the suggestions!  The first:last! strategy is good in that it ensures added sheets are included in the 3D range.  I hadn't come across the INDIRECT/CELL strategy, nor the double-click to force a recalc (F9 also works but perhaps lacks the elegance).

I worked through your formula to see what it was doing and came up with an alternative test to check that the user has clicked a company from the list.

= LET(
    ref, INDIRECT(CELL("address")),
    selected, IF(
      ISREF(ref distinctCompanies), 
      ref, 
      "doubleclick any cell then single click on a company"
    ),
    selected
  )

PeteSauces 

I have used a 3D range to group the various data tables.  That requires the tables to be identically positioned on each sheet.  Other options are available but require lists of source data to be maintained.

= LET(
    consolidatedList, TOCOL(productLists, 1),
    FILTER(
      consolidatedList,
      ISNUMBER(SEARCH(selectedCompany, consolidatedList))
    )
  )


The defined name "productLists" refers to the 3D range
=Products:Clearance!$A$2:$A$21

PeteSauces 

If you don't see this option 

put the file on OneDrive or like resource, share with everyone and post the link here.