Forum Discussion
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 highlight any name that occurs more than twice across the entire book - so I need the values to be individually counted between each sheet and then highlight on all sheets if they are listed more than twice.
Please and thank you.
2 Replies
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
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
