Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a workbook that has two worksheets.

The first worksheet is simply a list of the first 78,498 prime numbers in a single column, A1-A78498.

The second worksheet has a grid of numbers from 1 to n.

The goal is to highlight the cells with prime numbers in the grid by referencing the prime number values in the other worksheet. Is this possible, and if so, how?


I have named the column with my prime numbers "PRIMES1T".

I would like the formula to work for the entire worksheet, regardless of size, but my excel-fu is extremely weak. If at all possible, I would like to be able to enter the formula in the dialog box for conditional formatting (as below): I have tried =NOT(ISNA(MATCH(A:Z,PRIMES1T,0) (only A-Z, but have to start somewhere) with no luck.

share|improve this question
See also this question. – Karlo Apr 25 at 7:47
up vote 2 down vote accepted

in conditional formatting, to reference a range on another worksheet (or workbook) you need to use a named range Assuming you name your prime numbers range rPrimes then the conditional format formula would be (for cell A1)

share|improve this answer
neilson: how would I get this to work for the entire spreadsheet, regardless of size? I tried =NOT(ISNA(MATCH(A:Z, PRIMES1T, 0) (not everything, I know, but have to start somewhere) to no avail. – ScottSEA Mar 15 '11 at 7:01
@ScottSEA just put the formula as written above on cell A1 then copy paste special format to all cells you want it applied to. while you could apply it to every cell in the sheet thats probably not a good idea as it would be quite intensive calculation! – chris neilsen Mar 15 '11 at 11:45
Thanks very much! – ScottSEA Mar 15 '11 at 16:14
@ScottSEA happy to help. if this is the answer you were looking for, you should accept it (click the tick) – chris neilsen Mar 15 '11 at 19:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .