Formula help, IF change base upon a cell.

[mduntley]

Hello, I am working on a big document. I got a formula that helps me with one column, but I am wondering if I can make this more user-friendly. This is the formula

=IF(O7="","",IF(O6="","",IF(O7=O6,"","ERROR")))

I am wondering if there is a way if I edit a cell, let say b2 that the Bold O will change. For example

b2=O
then my formula will be
=IF(O7="","",IF(O6="","",IF(O7=O6,"","ERROR")))

b2=N
then my formula will be
=IF(N7="","",IF(N6="","",IF(N7=N6,"","ERROR")))

 

[Special-K99]

Either

=IF(IF($B$2="O",O7,N7)="","",IF(IF($B$2="O",O6,N6)="","",IF($B$2="O",IF(O7=O6,"","ERROR"),IF(N7=N6,"","ERROR"))))

or

=IF(INDIRECT($B$2&"7")="","",IF(INDIRECT($B$2&"6")="","",IF(INDIRECT($B$2&"7")=INDIRECT($B$2&"6"),"","ERROR")))

However, if you're going to copy these formulas down a column the 6s and 7s will change and only the first formula will work

 

[wideboydixon]

Using a letter you're forcing using INDIRECT which isn't great but will work:

=IF(AND(INDIRECT(B2&"6")<>"",INDIRECT(B2&"7")<>"",INDIRECT(B2&"6")=INDIRECT(B2&"7")),"ERROR","")

If you're prepared to use the column number instead then it's a little neater as the formula isn't volatile. You can also cut out a condition to streamline it:

=IF(AND(INDEX($6:$6,B2)<>"",INDEX($6:$6,B2)=INDEX($7:$7,B2)),"ERROR","")

Then B2 would contain 14 for "N", 15 for "O" etc.

WBD

 

[palaeontology]

MAYBE TRY THIS ...

=IF(OR(INDIRECT(B2&"6")="",INDIRECT(B2&"7")=""),"",IF(INDIRECT(B2&"6")=INDIRECT(B2&"7"),"","ERROR"))

KIND REGARDS,

cHRIS

 

[mduntley]

MAYBE TRY THIS ...

=IF(OR(INDIRECT(B2&"6")="",INDIRECT(B2&"7")=""),"",IF(INDIRECT(B2&"6")=INDIRECT(B2&"7"),"","ERROR"))

KIND REGARDS,

cHRIS

This is good, but when i try to copy it down, 6 and 7 stays the same, even when i removed the "". My goal is to find any price different between two cell that has the same criteria in a different a cell.

 

[mduntley]

So I made a quick spreadsheet what my whole attention is

[TABLE="class: grid, width: 462"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fruite[/TD]
[TD]Store 1[/TD]
[TD]Store 2[/TD]
[TD][/TD]
[TD]formula if f2=B[/TD]
[TD]formula if f2=c[/TD]
[TD]formula if f2=d[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]$ 2.00[/TD]
[TD]$ 2.00[/TD]
[TD]$ 3.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]error[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]$ 3.00[/TD]
[TD]$ 3.00[/TD]
[TD]$ 9.00[/TD]
[TD]Error[/TD]
[TD]ERROR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]$ 2.00[/TD]
[TD]$ 2.00[/TD]
[TD]$ 7.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]error[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD]$ 4.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]ERROR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]$ 2.00[/TD]
[TD]$ 5.00[/TD]
[TD][/TD]
[TD]Error[/TD]
[TD]ERROR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bananna[/TD]
[TD]$ 4.00[/TD]
[TD]$ 6.00[/TD]
[TD]$ 4.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So this Column e-g is the result i want, but it is in one column. If a cell is blank, such as the orange in column d, 2 of them are blank so there is no error. My project is to see if there is a price difference between 2 stores, if it has a price.

 

[wideboydixon]

I *think* I understand. How about entering the store name in E1 and using a little "helper" in E2 like this:

Book1
	A	B	C	D	E
1					Store 1
2	Fruit	Store 1	Store 2	Store 3	1
3	Apple	$2.00	$2.00	$2.00
4	Orange	$3.00	$3.00	$9.00	ERROR
5	Apple	$2.00	$2.00	$7.00
6	Orange		$4.00
7	Orange	$2.00	$5.00		ERROR
8	Banana	$4.00	$6.00	$4.00
Sheet1

Cell Formulas
Range	Formula
E2	=MATCH($E1,$B$2:$D$2,0)
E3	=IF(INDEX($B3:$D3,$E$2)="","",IF(SUMPRODUCT(($A$3:$A$8=$A3)*(INDEX($B$3:$D$8,,$E$2)=INDEX($B3:$D3,$E$2)))=COUNTIFS($A$3:$A$8,$A3,INDEX($B$3:$D$8,,$E$2),"<>"),"","ERROR"))

WBD

 

[mduntley]

I *think* I understand. How about entering the store name in E1 and using a little "helper" in E2 like this:

	A	B	C	D	E
Store 1
Fruit	Store 1	Store 2	Store 3
Apple
Orange	ERROR
Apple
Orange
Orange	ERROR
Banana

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$2.00[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]$2.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$3.00[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$9.00[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]$2.00[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]$7.00[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$4.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]$2.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$4.00[/TD]
[TD="align: right"]$6.00[/TD]
[TD="align: right"]$4.00[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=MATCH($E1,$B$2:$D$2,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=IF(INDEX($B3:$D3,$E$2)="","",IF(SUMPRODUCT(($A$3:$A$8=$A3)*(INDEX($B$3:$D$8,,$E$2)=INDEX($B3:$D3,$E$2)))=COUNTIFS($A$3:$A$8,$A3,INDEX($B$3:$D$8,,$E$2),"<>"),"","ERROR"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



WBD

This works, thank you.