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.

 

[awoohaw]

Have you looked at the SUBTOTAL or AGGREGATE functions instead of SUM?
Do you need to include the 4th row of the SUM columns in the first argument of the IF Function?

 

[awoohaw]

Have you looked at the SUBTOTAL or AGGREGATE functions instead of SUM?
Do you need to include the 4th row of the SUM columns in the first argument of the IF Function?

SOrry, I did not get a chance to add this before the editing time expired:
Would this work:

Book1
	B	C
1
2	Receivable 1	2
3	Receivable 2	2
4	Receivable 3	2
5	Sum Receivables	6
6
7
8
9	Payable 1	1
10	Payable 2	1
11	Payable 3	1
12	Sum Payables	3
13
14	DUE FROM	3
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	="Receivable " & ROW()-1
C5,C12	C5	=SUM(C2:C4)
B9:B11	B9	="Payable " & ROW(B9)-8
B14	B14	=IF(C12>C5,"DUE TO",IF(C12<C5,"DUE FROM",""))
C14	C14	=ABS(IF(C12>C5,C5-C12,IF(C12<C5,C12-C5,0)))

 

[Peter_SSs]

Would this work:

I don't think so, at least as I understand what the OP is trying to do. Try changing cell C10 to say 12.

@irfananeeza
Your cell references are very hard to follow. I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. It also makes it easy for us to copy your sample for testing.

See if something like this does what you want.

24 01 31.xlsm
	B
1
2	2
3	2
4	2
5
6	6
7
8	1
9	1
10	1
11	3
12	6
13
Balance

Cell Formulas
Range	Formula
B5	B5	=IF(SUM(B2:B4)>SUM(B8:B10),"",SUM(B8:B10)-SUM(B2:B4))
B6,B12	B6	=SUM(B2:B5)
B11	B11	=IF(SUM(B8:B10)>SUM(B2:B4),"",SUM(B2:B4)-SUM(B8:B10))

24 01 31.xlsm
	B
1
2	2
3	2
4	2
5	6
6	12
7
8	1
9	10
10	1
11
12	12
13
Balance

Cell Formulas
Range	Formula
B5	B5	=IF(SUM(B2:B4)>SUM(B8:B10),"",SUM(B8:B10)-SUM(B2:B4))
B6,B12	B6	=SUM(B2:B5)
B11	B11	=IF(SUM(B8:B10)>SUM(B2:B4),"",SUM(B2:B4)-SUM(B8:B10))

 

[awoohaw]

Here is another try at it:

Book1
	B	C
2	Receivable 1	3
3	Receivable 2	3
4	Receivable 3	3
5	DUE TO	3
6	Total Receivables	12
7
8
9	Payable 1	4
10	Payable 2	4
11	Payable 3	4
12
13	Total Payables	12
Sheet1

Cell Formulas
Range	Formula
B5	B5	=IF(SUM(C9:C11)>SUM(C2:C4),"DUE TO","")
C5	C5	=IF(SUM(C9:C11)>(SUM(C2:C4)),ABS((SUM(C2:C4))-SUM(C9:C11)),"")
C6,C13	C6	=SUM(C2:C5)
B12	B12	=IF(SUM(C9:C11)<SUM(C2:C4),"DUE FROM","")
C12	C12	=IF(SUM(C9:C11)<SUM(C2:C4),ABS(SUM(C9:C11)-SUM(C2:C4)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:C5	Expression	=$C5>0	text	NO
B12:C12	Expression	=$C12>0	text	NO

 

[Peter_SSs]

Here is another try at it:

If you tweak the inequality signs &/or the subtraction order you can drop the ABS function.
.. but then it would look very familiar to me.

 

[awoohaw]

If you tweak the inequality signs &/or the subtraction order you can drop the ABS function.
.. but then it would look very familiar to me.

Sorry @Peter_SSs , the web page didn't show your comments until I went away from the page and came back. I need to remember to press F5 to refresh the entire page

 

[irfananeeza]

Thanks to both [B]awoohaw[/B] and [B]Peter_SSs[/B]. The Due To/From formula helped. However, the SUM formula does not help the way I want to balance. Maybe its the way I presented the problem that confused you. I am reviewing the guidelines for xl2BB right now so I can repost the exact problem. So, you have a better idea of the issue and the solution.

 

[Peter_SSs]

I am reviewing the guidelines for xl2BB right now so I can repost the exact problem. So, you have a better idea of the issue and the solution.

OK, that should help clarify.

 

[irfananeeza]

Hi Team, I am reposting my data. I need a dynamic formula that determines the balancing number and put it into the correct yellow cell. This will balance the totals in grey i.e. B8 = B23. For e.g., value in cell B30 and B31 provides me the balancing number and I have to input it manually. My objective is not to input manually instead it gets done automatically. Thanks for your help in advance.

Formula_to_Balance_BalanceSheet.xlsx
	A	B	C
1	Assets	in 2023
2	BS - RBC Busines Cheking1	37,781	Value is coming from other sheet
3	BS - Prepaid Expense	0	Value is coming from other sheet
4	BS - Deposit Paid by You	0	Value is coming from other sheet
5
6	Description appears when applies		Field should auto populate based on formula results
7
8	Total Assets	37,781
9
10	Liabilities
11	BS - Master Card	0	Value is coming from other sheet
12	BS - Customer Deposits Received	0	Value is coming from other sheet
13	BS - Loan Payable	0	Value is coming from other sheet
14
15	BS - Due to Shareholder		Field should auto populate based on formula results
16
17	Total Liabilities	0
18
19	Shareholder Equity
20	Retained Earnings Closing Balance	-7,482	from cell B28
21
22	Total Shareholder Equity	-7,482	Sum Formula
23	Total Liabilities & Shareholder Equity	-7,482	Sum Cell B15 + Cell B22
24
25	Retained Earnings
26	Opening RE	-15,637
27	Net Income (Loss)	8,155
28	Closing Balance	-7,482
29
30	Due from shareholder	FALSE	Manually Input Value in Yellow cell B6
31	Due to shareholder	45,263	Manually Input Value in Yellow cell B15
Sheet1

Cell Formulas
Range	Formula
B1	B1	="in"&" "&YEAR('https://d.docs.live.net/23da963b31d0f08e/Desktop/Tools ^0 Training/Tools ^0 Training/Bookkeping Templates for Silver Fox/[Corporate Bookkeeping Template_v3 - Copy - Copy.xlsx]Instructions'!$B$7)
A2	A2	='https://d.docs.live.net/23da963b31d0f08e/Desktop/Tools ^0 Training/Tools ^0 Training/Bookkeping Templates for Silver Fox/[Corporate Bookkeeping Template_v3 - Copy - Copy.xlsx]Trial Balance'!B2
A3:A4	A3	=+'https://d.docs.live.net/23da963b31d0f08e/Desktop/Tools ^0 Training/Tools ^0 Training/Bookkeping Templates for Silver Fox/[Corporate Bookkeeping Template_v3 - Copy - Copy.xlsx]Trial Balance'!B11
A6	A6	=IF(SUM(B2:B4)<SUM(B11:B13),"BS - Due from Shareholder","Description appears when applies")
B8,B17	B8	=SUM(B2:B6)
A12	A12	=+'https://d.docs.live.net/23da963b31d0f08e/Desktop/Tools ^0 Training/Tools ^0 Training/Bookkeping Templates for Silver Fox/[Corporate Bookkeeping Template_v3 - Copy - Copy.xlsx]Trial Balance'!B16
A13	A13	=+'https://d.docs.live.net/23da963b31d0f08e/Desktop/Tools ^0 Training/Tools ^0 Training/Bookkeping Templates for Silver Fox/[Corporate Bookkeeping Template_v3 - Copy - Copy.xlsx]Trial Balance'!B18
A15	A15	=IF(SUM(B2:B4)>SUM(B11:B12),"BS - Due to Shareholder","Description appears when applies")
B20	B20	=+B28
B22	B22	=+SUM(B20:B20)
B23	B23	=+B17+B22
A27	A27	=+'https://d.docs.live.net/23da963b31d0f08e/Desktop/Tools ^0 Training/Tools ^0 Training/Bookkeping Templates for Silver Fox/[Corporate Bookkeeping Template_v3 - Copy - Copy.xlsx]Income Statement'!A31
B28	B28	=+SUM(B26:B27)
B30	B30	=+IF(B8<B23,-B23)
B31	B31	=+IF(B8>B23,B8-B23)