Dynamically change formula range

[119luke]

Hi All,

I have the following data
A B C D E F G H I J K L M N O P
D A A D D D
D D D D D D D D
D A
D D D D D D D D D A


I have to compare the value in column A with the rest of the columns in the same row. If any of the values from B to K is different than the value in A, then I have to display "Incon" in result. If all the values from B to K is same as that in A, then I have to display "Con" in result. I am testing out the following formula =IF(OR(C1<>D1:F1),"Incon","Con") but when there are spaces in some columns the formula is not working. Can we dynamically set the range (end point) so that my formula won't compare the value in A with spaces?

Thanks
Luke.

[FDibbins]

Hi, welcome to the forum

Try this, copied down...
=IF(COUNTIF(B1:J1,A1)=COUNTA(B1:J1), "con","incon")

[119luke]

That worked. Thanks. But just for future reference, is there any way to dynamically change the end column name when we copy down a formula?

[FDibbins]

That worked. Thanks. But just for future reference, is there any way to dynamically change the end column name when we copy down a formula?

Not sure I understand what you mean?
Do you want the J to be dynamic?

[119luke]

Not sure I understand what you mean?
Do you want the J to be dynamic?

Yes. That's what I meant. Is it possible?

[FDibbins]

=IF(COUNTIF(B1:J1,A1)=COUNTA(B1:J1), "con","incon")

Why would you need to make this dynamic? If you take the range up to the cell just before the formula, it will work, no matter how many entries you have

[congnt92]

Hi 119luke,
Plz try this
Q1=IF(SUMPRODUCT((A1=OFFSET($B1,,,,LOOKUP(2,1/(TRIM(CLEAN(SUBSTITUTE(B1:P1," ","")))<>""),ROW($1:$100))))*1)=LOOKUP(2,1/(TRIM(CLEAN(SUBSTITUTE(B1:P1," ","")))<>""),ROW($1:$100)),"con","incon")

[FDibbins]

congnt92 nice formula

BUT, you have already defined a range there (B1:P1), so, depending on what else may be within that range, you could just use that range as is, in my suggested formula.

No matter how you look at this, you need to define a range somewhere, somehow.