Forum Discussion
macklemic
Apr 28, 2025Copper Contributor
Highlighting values across a book
I'm not certain if this is something that requires a formula, special coding, or what. I have a excel book with a series of sheets regarding assignments for roughly 400 employees. I need to highli...
m_tarler
Apr 29, 2025Bronze Contributor
since you only have about 400 employees you should be able to create a helper list and then do a countif on that list. The first trick will be how to create the helper list. Let's say you have names in column A on sheets A-Z and confident no single sheet has more than 100 names/rows then you could use:
=LET(n, VSTACK('a:z'!A1:A100), FILTER(n, n<>""))
Now that temp list could be on a sheet or defined in as a NAME. For this example lets say you defined it as NameList. Then you can use the following formula in the conditional formatting on each sheet:
=COUNTIF(NameList, A1)>2
or if you have the helper column on sheet 'reference' in cell A1 then
=COUNTIF('reference'!$A$1#, A1)>2