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

A	B	C	D	E	F	G
					b
Fruite	Store 1	Store 2		formula if f2=B	formula if f2=c	formula if f2=d
Apple	$ 2.00	$ 2.00	$ 3.00			error
Orange	$ 3.00	$ 3.00	$ 9.00	Error	ERROR
Apple	$ 2.00	$ 2.00	$ 7.00			error
Orange		$ 4.00			ERROR
Orange	$ 2.00	$ 5.00		Error	ERROR
Bananna	$ 4.00	$ 6.00	$ 4.00

<tbody>
</tbody>

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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell 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")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

WBD

This works, thank you.