An Array formula in Condtional Formating??? 3

[Wray69]

Is there anyway to put an Array Formula into CONDITIONAL FORMATING?

I havent been able to do it, hoping someone might have an idea on how to do it.

Regards -

Wray

 

[DaleWatson123321]

Wray,

An alternative ???...

I suppose you've considered the option of placing your array formulas in cells as you would normally. And then through Conditional Formatting, reference the values generated by the array formulas ???

If this isn't workable, perhaps you could provide more info on your objective, as it might help generate other suggestions of possible "work arounds".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[KenWright]

Don't believe you can do so - You certainly can't use an array constant, and any attempt to array enter a formula will simply close the dialog box.

Is it possible there is another way to do what you want, that a few more details will help with.

Regards
Ken.............

 

[Wray69]

Hi Dale,

Yeah, I originally placed the array formula in the column next to it and hid it but the person I am doing it for didnt like that.

What I am doing is they are entering checks and they need to make sure that they dont enter any duplicate numbers. So I was placing this array formula =IF(ISBLANK(F4)," ",IF(OR(F4=$F3:F$3,F4=F5:F26),1,0) in the column nect to the one with the check numbers and then using conditional formating to turn the cell red if the number entered already exsisted, which he liked but didnt like the extra hidden column.... I greatly appreciate any help that you can give....

Regards -

Wray

 

[DaleWatson123321]

Wray,

The first thing that comes to mind, is to place the array formula on a SEPARATE sheet, and even hide/protect the sheet.

Then, for example, if you name your array formula "dups", then you could have this formula in your Conditional Formatting cell... Formula is: =dups>0

I hope this can work for you.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

Try this formula in conditioal formatting:

  =MATCH(F1,$F$1:$F$2000,0)<ROW(F1)

Put it in Cell F1 and then copy it down as far as you need to go. If more than 2000 rows, then change the range reference.

Or better still, set a range name (e.g. "DUPCHECK&quot to $F$1:$F$2000 or whatever and use

  =MATCH(F1,DUPCHECK,0)<ROW(F1)

 

[KenWright]

One more way, assuming your data in Col A:-

Cond Formatting - Formula is

=COUNTIF($A$1:$A$10000,A2)=2

Choose a bright red pattern

Regards
Ken...............

 

[Wray69]

Hey Everyone,

Thanks for all the great ideas, you all got me onto the right path....

Regards -

Wray