Changing cells colors in relation to cell input - NOT conditional formatting ques.

Hi guys, hope someone here can help me out!

I'm a average excel user, i know my way around it and kow common and basic formulee, VB isnt a strong point, but i am willing to attemp anything for this, let me explain

Lets say in colum A I have a list of items, in colum B i have a check box, currently the check box is red, if i tpye a x it turns green due to conditional formating. That works fine...onto the problem...

Lets say in colum A i have my items but they are repeated...eg A1, A145, A166, A122 are all the same item, what i want is regardless of where i place the x each check box under coloum B either gets a X and turns green, or just turns green, i dont mind...

I dont think conditional formatting can cut this, though i may be wrong. Ive tried to use the IF function under condtional formatting but ran into problems...

Any help or direction would be great!

The following might help although I don't use "check boxes" (the little boxes you can tick) to place the "x". I am assuming that you can use blank cells and enter an "x" into one of them which in turn will fill in all of the other relevant cells.

You need to insert a 'Helper column'. This MUST be at the left of the values that you currently have in column A (so all of your data currently in column A will now be in column B and your "automatic x marks" will be displayed in column C). The idea of this helper column is that you'll type an "x" into a cell 9ssay A7) and if there are repetitions of the value contained in cell B7 in any other B cells that an X will be automatically entered into the corresponding column C cells.

If you type the following formula into cell C1 (assuming that your data is contained in the range A1 to B5) see how it works:

=IF(OFFSET(A1,0,1)=VLOOKUP("x",$a$2:$b$5,2,FALSE),"x","")

However, I couldn't make it work for multiple repetitions of different numbers eg where multiple cells contain the value 345 and some other multiple cells contain 678 it won't display ALL the repetitions of ALl values- it'll only show an x for ALL 345 values OR all 678 values but not both simultaneously...sorry!

hmm intresting...

So this kinda works, but then doesn't. I havent tested it out yet as the spreadsheet is at work.

But from whta i can gather it will only work once, for the first X you'd put in Column A.

So in column B if i have the word 'milk' repeated 6 times, and the word 'eggs' repeated 6 times, and the word 'butter' 6 times, i would only update column C ( the 'check' box column ) of the first instant...

Is there anyone else with ideas?

For the conditional format formula, =SUMPRODUCT((A:A=A1)*(B:B))>0. With this, for any given row, if the value in A of that row appears anywhere in A and has checkbox next to it that is checked (I'm assuming the value of the checkbox is also in B, either TRUE or FALSE), then this will format any cell with that value in A as you want it. There is no non-macro way to change the value itself, as a formula cannot refer to the cell it is in.

that sounds promising, i'll have to try it out...my check boxes arent the littel box you click and a x appears, what i mean is a cell with the letter x written in it.

would this make any differnece?

Change formula to =SUMPRODUCT((A:A=A1)*(B:B="x"))>0.