# Check values cells with Formula or vba?

• Hello,

I have 7 cells to be controlled
T2 - T22 - T42 - T62 - T82 - T102 - T122
Values can be 0 or 1
When values are respectively
1-0-0-0-0-0-0 then Cell W2 = 1
When values are respectively
1-1-0-0-0-0-0 then Cell W2 = 0 W22 = 1
When values are respectively
1-1-1-0-0-0-0 then Cell W2 = 0 W22 = 0 W42 = 1
.
.

When values are respectively
1-1-1-1-1-1-1 then Cell W2, W22...W102 = 0 W142 = 1
How can this be done?
Thanks for the help.
aisietie

• Re: Check values cells with Formula or vba?

Hi aisietie,

It looks from your example as though there will not be zeros in the first cells, with 1s subsequently. If this is the case, then try in the relevant cells of column W:

=--(SUM(\$T\$2,\$T\$22,\$T\$42,\$T\$62,\$T\$82,\$T\$102,\$T\$122,\$T\$142)=((ROW()+18)/20))

although I'm not sure why the final cell in column W is W142 and not W122.

Hope this helps.

[COLOR="Purple"]Regards,[/COLOR]
[COLOR="Purple"]Batman.[/COLOR]

• Re: Check values cells with Formula or vba?

Batman,

That works OK
The W142 is not correct. W122 is correct.
What is there to be added - changed, when the possibility exists that the 1 st cell has a 0.
it can occur,
Thanks 4 the note, and thanks 4 the solution.

aisietie

• Re: Check values cells with Formula or vba?

Not nearly as compact as Batman's, but it seems to work.

• Re: Check values cells with Formula or vba?

Just another way of doing the same thing.

• Re: Check values cells with Formula or vba?

If vba, try

• Re: Check values cells with Formula or vba?

Have you tried my original solution when the first value is zero? It seems to work. Although you don't say, I assume that if the T2 value is zero, the result in W2 also needs to be zero.

However, an alternative formula (provided that there is nothing in cell T142) would be =IF(AND(T2=1,OFFSET(T2,20,0)=0),1,0).

Both of these are based on the assumption that you will not have any of the earlier cells with a value of zero, followed by later cells with a value of 1, e.g. 0-0-1-1-0-1-0.

[COLOR="Purple"]Regards,[/COLOR]
[COLOR="Purple"]Batman.[/COLOR]

• Re: Check values cells with Formula or vba?

Batman, tinbendr, S O, jindon,

Thanks 4 the different solutions.
My question is well answered, and now I am looking wich solution I will take.

I can only say, thank you verry much.

aisietie

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!