Formulas and Conditional formating

[Matthew]

Does anyone know how to make a formula like this - =COUNTIF
($A$2:$A$11,A2)>1 - work across multiple spreadsheets?

Thanks

Matthew

 

[Dave R.]

By going here,

http://longre.free.fr/english/

downloading the morefunc package, and using

=COUNTIF.3D()

 

[Matthew]

thanks for this but it is now saying that "You May Not use
references to other worksheets or workbooks from the
Conditional Formating Criteria" the the formula i use
is "=COUNTIF.3D(Long streetDA!A$4:A$323,A5)>1" does it
look correct to you?

Matthew

 

[Dave R.]

Sorry, I didn't make the connection between the conditional formatting and
your other question. Now, you do not need to use COUNTIF.3D since you are
not 'making it work across multiple worksheets'.

One solution, found here:

http://tinyurl.com/24pp5

is to enter your countif formula referring to some named range,

e.g.
conditional format: =COUNTIF(DDA,A5)>1 and set your format.

then jump over to DDA sheet, select A4:A323, and go to insert>name>define>
and enter DDA, and apply.

Now jump back over to your sheet, and your format should be applied (if
true).

 

[Matthew Lake]

sorry about this but i seem to be having alot of trouble
with this spreadsheet i have 60 worksheets in the file i
cannot seem to get it to work across even two of them.

Matthew

 

[Dave R.]

If all else fails, use that COUNTIF.3D formula in some cell (not conditional
formatting), then set the conditional format to look at the cell containing
the COUNTIF.3D formula..

 

[Matthew]

I've tried this but it just shows the formula the formula
i used is - =COUNTIF.3D("'other codes:764'!",A4:A400)>1
I think it looks correct but I doesn't work

Matthew

 

[Dave R.]

This is taking awhile isn't it? If it's just showing the formula, the cell
is probably formatted as text. Go to some cell, right click and format it as
general, then click on the formula bar and type in that formula - don't copy
it from another cell unless you are copying it from the formula bar.