Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

edit

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
add comment

1 Answer

up vote 1 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)

=NOT(ISNA(MATCH(A1,rPrimes)))
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.