Conditional Formatting

Dear Sir

How can i turn the "Sources data" into black when the data in cell match the "range of cell"

See attached

it used =MATCH(A8,$A$8:$D$18,0) but invain , if only single column, it is ok
What i want to do is , when i select the data whichis the same as in A8:D:, then turn color so that we will not select anymore

appreciated any help

FDibbins

i think i did not get it right

See attached

Only, the 1st cell work

If the selections are ALL in column G:

=MATCH(A8,$G$8:$G$100,0)

If selection are in colums G: J

then created DV with a range for each column

Still not working, i think i did not get the concept right

I selected the A8:D18, then go to CF, input formula :=MATCH(A8,$G$8:$J$18,0)

it does not work

Select A8, then use the first formula:

=MATCH(A8,$G$8:$G$100,0)

For column H repeat the above but change range to H from G

Repeat for I and J

you can try: =MATCH(A8,G$8:G$18,0)
but it will work for column
ie. if Guest 4 has F39 and it will doesn't exist in 4th column from first range but exist in any other - nothing happen, whatever better see attachment

Dear Sandy

it iit snot working on my side, i am alternatively taken the advice of John.

However, i wonder if i can do =MATCH(A8,G$8:J$18,0)

I wonder if i am wrong that it is not possible

Eric

Could you show example what you want to achieve? Without any CF and other things, only manually colored appropriate cells.

Dear Sandy

Please find attached

Appreciated in advance

So blue on Sheet 2 is correct?

Sandy

Yes

and how many guests there will be? 10 or 10.000 ?

Try this as DV rule:

=COUNTIF($G$11:$J$16,A11)

Applies to: $A$11:$D$21

Sandy

it will be around 855 persons

I think that's all what John did in post #15

No idea why, it is not working

Sandy

Yes, i tried John's idea It works if i do that column by column. I wonder if there is a better way

See attached.

Sandy

thanks and it does serve the purpose,

So i used a wrong formula =match!

But in the DV, can i restrrict the duplicated input ! As the seat no is unique, so it can prompt to stop/alert before hand

How can i do that ?

Seats are not unique????? AFAIK they are unique always...
But if you want...
Set DV Error Alert Style to Warning
or
simply don't use DV for conditions

else I misunderstood

Sandy

i got some problem with real world.
No idea why it does not work

any idea on the DV part.. =COUNTIF($H$2:$M$11,H2)=1

Appreciated your help

Sandy

thanks and i found an interesting finding, after i set the DV

The H2 column and I3 column same valid, it does not prompt. I copy the H2 value to I3 column, it does not prompt too. For Example, if i have F35 in H2 (that is a value create by Match fucntion, it does not alert the DV, and i copy the cell and paste value only to I2, it also does not work

Is that anything that i missed ?

Did you read post #27 ?
I think you should read again