Workaround Formula to prevent Circular Reference Error

[irfananeeza]

Hi,
I am looking for a formula solution.
Problem: I want to balance cell B5 with B14. I use formula =IF(B5>B14,B5-B14). I want the formula to put the resulting value within cell B13 as shown in cell B13 in red color and formula to update cell A13 value as "Due To". The total for cell BI4 should be updated to "6". =IF(B5<B14, B5-B14), then I want formula to put the resulting value in cell B4 in red color and formula to update cell A4 as "Due From". The final result should make both cells balance i.e. cell B5 = cell B14.
Issue: When I use IF formula, in any of these cells, it gives me circular reference error with a blue line. I fount it on google that I can turn off circular reference. However I do not want to do that.
Expectation: I am looking for either some kind of dynamic formula to serve my purpose or a work around to help me with balancing numbers.
I would be greatful if you can help me with the desired result.
Thanks.

 

[Peter_SSs]

From what I can see it is pretty much what we had before except that the second balancing cell is in the middle of that section of data rather than at the bottom.
Try these modifications.

24 01 31.xlsm
	B	C
1	in 2023
2	37,781	Value is coming from other sheet
3	0	Value is coming from other sheet
4	0	Value is coming from other sheet
5
6		Field should auto populate based on formula results
7
8	37,781
9
10
11	0	Value is coming from other sheet
12	0	Value is coming from other sheet
13	0	Value is coming from other sheet
14
15	45,263	Field should auto populate based on formula results
16
17	45263
18
19
20	-7,482	from cell B28
21
22	-7,482	Sum Formula
23	37,781	Sum Cell B15 + Cell B22
24
25
26	-15,637
27	8,155
28	-7,482
Balance (2)

Cell Formulas
Range	Formula
B6	B6	=IF(SUM(B2:B4)>(SUM(B11:B13)+B22),"",SUM(B11:B13)+B22-SUM(B2:B4))
B8,B17	B8	=SUM(B2:B6)
B15	B15	=IF((SUM(B11:B13)+B22)>SUM(B2:B4),"",SUM(B2:B4)-SUM(B11:B13)-B22)
B20	B20	=B28
B22	B22	=SUM(B20)
B23	B23	=B17+B22

 

[irfananeeza]

Thanks @ Peter_SSs. The formula solved my problem. Thanks for helping out.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.