VBA to add a Value From on Cell to the End of a Formula

[Kscoof]

I have a formula in column AV that is summing some values in a table and another formula in AW that is summing some different values in the same table. I cannot sum Columns AV and AW together in the same cell as it creates a circular reference because I am using a sumif as part of the equation and they are in the same array. I want to create a macro that will add the value in column AW to the corresponding cell in AV. The columns go from row 14 to row 74. For example, if the calculation changes in cell AW21 then I want it to add the value of AW21 to AV21 at the end of the formula. I need to keep the formula in column AV and can only have one result at a time to the end of the formula in column AV. So when there is a change the old result needs removed. Here is the macro I have so far. It will add the value to the end of the formula but won’t remove the old. Also, it won’t add a value when the calculation in AW changes. I must hand enter a number for it to work. I have tried a bunch of different things but below is what I have currently. Any suggestions are appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("AW14:AW74")) Is Nothing Then

If IsNumeric(Target.Value) Then

With Target.Worksheet.Range(Target.Address)

.Offset(0, -1).Formula = .Offset(0, -1).Formula & "+" & Target.Value

End With

End If

End If

End Sub

 

[StephenCrump]

Also, it won’t add a value when the calculation in AW changes. I must hand enter a number for it to work.

Yes, a change in the result of a formula doesn't trigger a Worksheet_Change.

I cannot sum Columns AV and AW together in the same cell as it creates a circular reference because I am using a sumif as part of the equation and they are in the same array.

Rather than applying a VBA patch, wouldn't it be better to get the results you want into a formula in the first place? Can you illustrate what you're trying to do?

 

[Kscoof]

This is a retirement financial model. I have a formula in column AV that is summing all the money that will be going back into the main brokerage account. This money is added to this column when RMD (required minimum distributions) is greater than the money needed for the year or when there is an investment contribution from the investment contributions table. There is another formula in column AW that is summing all the real estate sold. The issue is this money also needs to be added to the main brokerage account and I cannot sum Columns AV and AW together in the same cell as it creates a circular reference. I need a way to add the value of the cells in AW to AV. I think a vba macro that will add the value in column AW to the corresponding cell in AV will probably work. It needs to change the values every time the calculation in column AW changes. I will upload part of the sheet as an example. I will upload what I can in the post below. The file is too big to do XL2bb without modifying.

 

[Kscoof]

Net Worth Template_Filing Jointly_KS v11 rev 7.xlsm
	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
13	$113,429	$742,822	$8,321	$64,514	$17,599	$5,238	$4,984	$10,000	$12,000	$500,000	Excess Money to Main Brokerage	Sale of Real Estate
14	$142,246	$801,706	$127,424	$66,856	$18,238	$10,428	$5,165	$10,350	$12,420	$517,500	$118,801	$0
15	$180,994	$911,406	$257,724	$73,307	$19,998	$16,499	$5,663	$10,712	$12,855	$535,613	$118,004	$0
16	$194,097	$1,004,255	$285,121	$79,678	$21,736	$20,071	$6,155	$11,087	$13,305	$554,359	$5,000	$0
17	$110,979	$996,020	$282,783	$79,025	$21,557	$19,907	$6,105	$11,475	$13,770	$573,762	$0	$0
18	$5,299	$1,111,858	$315,670	$88,215	$24,064	$22,222	$6,815	$11,877	$14,252	$593,843	$0	$0
19	-$51,186	$1,154,512	$342,313	$95,660	$26,096	$24,097	$7,390	$12,293	$14,751	$614,628	$0	$0
20	$0	$1,212,727	$377,434	$105,475	$28,773	$26,570	$8,148	$12,723	$15,267	$636,140	$0	$0
21	$0	$1,202,897	$392,909	$105,680	$29,953	$27,659	$8,483	$13,168	$15,802	$658,405	$0	$0
22	$0	$1,192,146	$409,961	$105,987	$31,253	$28,859	$8,851	$13,629	$16,355	$681,449	$0	$0
23	$0	$1,089,080	$419,923	$104,110	$30,791	$29,561	$9,066	$14,106	$16,927	$705,299	$0	$0
24	$0	$1,059,633	$435,167	$103,343	$30,662	$30,634	$9,395	$14,600	$17,520	$729,985	$0	$0
25	$0	$1,089,114	$477,160	$108,618	$32,333	$33,590	$10,301	$15,111	$18,133	$755,534	$0	$0
26	$0	$1,107,401	$518,625	$112,933	$33,731	$36,509	$11,197	$15,640	$18,767	$781,978	$0	$0
27	$0	$1,018,234	$514,373	$106,444	$31,921	$36,209	$11,105	$16,187	$19,424	$809,347	$0	$0
28	$0	$973,568	$574,194	$113,365	$34,128	$40,420	$12,396	$16,753	$20,104	$837,674	$0	$0
29	$0	$967,231	$622,656	$116,870	$35,327	$43,832	$13,443	$17,340	$20,808	$866,993	$0	$0
30	$0	$973,773	$686,541	$122,332	$37,140	$48,329	$14,822	$17,947	$21,536	$897,338	$0	$0
31	$0	$916,835	$714,689	$120,194	$36,676	$50,311	$15,429	$18,575	$22,290	$928,745	$0	$0
32	$0	$854,896	$745,706	$118,036	$36,219	$52,494	$16,099	$19,225	$23,070	$961,251	$0	$0
33	$0	$676,820	$763,827	$113,290	$34,981	$53,770	$16,490	$19,898	$23,877	$994,894	$0	$0
34	$0	$589,162	$791,554	$109,747	$34,105	$55,722	$17,089	$20,594	$24,713	$1,029,716	$0	$0
35	$0	$528,234	$867,939	$112,554	$35,196	$61,099	$18,738	$21,315	$25,578	$1,065,756	$0	$0
36	$0	$451,201	$943,363	$113,936	$35,876	$66,408	$20,366	$22,061	$26,473	$1,103,057	$0	$0
37	$0	$319,135	$935,627	$104,030	$33,038	$65,864	$20,199	$22,833	$27,400	$1,141,664	$0	$0
38	$0	$112,963	$1,044,441	$107,460	$34,414	$73,524	$22,548	$23,632	$28,359	$1,181,622	$0	$0
39	$0	-$18,261	$1,114,330	$107,103	$34,609	$79,729	$24,452	$24,460	$29,352	$1,222,979	$0	$0
40	$0	$0	$1,081,703	$108,175	$35,276	$87,909	$26,960	$25,316	$30,379	$1,265,784	$0	$0
41	$0	$0	$972,882	$102,005	$33,628	$91,513	$28,066	$26,202	$31,442	$1,310,086	$0	$0
42	$0	$0	$854,606	$95,806	$31,974	$95,485	$29,284	$27,119	$32,543	$1,355,939	$0	$0
43	$0	$0	$580,156	$87,606	$29,616	$97,805	$29,995	$28,068	$33,682	$1,403,397	$0	$0
44	$0	$0	$424,707	$80,599	$27,606	$101,356	$31,084	$29,050	$34,860	$1,452,516	$0	$0
45	$0	$0	$280,703	$78,427	$27,236	$111,137	$34,084	$30,067	$36,080	$1,503,354	$0	$0
46	$0	$0	$112,124	$74,923	$26,436	$120,794	$37,046	$31,119	$37,343	$1,555,971	$0	$0
47	$0	$0	-$90,226	-$26,470	-$3,514	$116,290	$36,742	$32,209	$38,650	$1,610,430	$0	$0
48	$0	$0	$0	$0	$0	-$243,688	-$202,673	-$202,673	-$202,673	-$202,673	$0	$1,740,134
49	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
50	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
Asset Cessation

Cell Formulas
Range	Formula
AL14:AL50	AL14	=(IF(AND(Q14<0,R14<Q14),ABS(R14)-ABS(Q14),IF(Q14>0,Q14,0))+(IF(F13<=0,0,IF(Q14>0,0,IF(OR($N$12="",$N$12="No Line"),0,IF(L14<=0,(MAX(ABS(R14),ABS(Q14))),0)))))+(IF(F13<=0,0,IF(Q14>0,0,IF(AND($N$12="Pay Off Every 5",N13<0,N12<0,N11<0,N10<0,N9<0,L14>0,L13>0,L12>0),-(ABS(N13)+(M14*(ABS(N13)))),0)))))+(SUMIF(Investment_Contributions_Tbl[Year],'Asset Cessation'!$A14,Investment_Contributions_Tbl[[Other Contribution/ Year: Client 1]:[Other Contribution/ Year: Client 1]]))+(SUMIF(Investment_Contributions_Tbl[Year],'Asset Cessation'!$A14,Investment_Contributions_Tbl[[Other Contribution/ Year: Client 2]:[Other Contribution/ Year: Client 2]]))
AM14:AM50	AM14	=IF(SUM(AB14:AK14)=0,0,(SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate",AB14:AK14,"<=0")+SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate Primary Res",AB14:AK14,"<=0"))+((SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate",AB14:AK14,"<=0")+SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate Primary Res",AB14:AK14,"<=0"))*$L$2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AB78:AK138,AB14:AK74,L14:Z74	Cell Value	<0	text	NO
AB78:AK138,AB14:AK74,L14:Z74	Cell	contains an error	text	NO

 

[Kscoof]

I had to erase some columns as the table was too large. Now the columns in question are AL and AM.

 

[Kscoof]

Net Worth Template_Filing Jointly_KS v11 rev 7.xlsm
	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
11	Main 401k	Main 401k	Main Brokerage	Main IRA	401K	Main Roth IRA	Main Roth IRA	Real Estate	Real Estate	Real Estate Primary Res
12	Jane Smith	John Smith	John Smith	John Smith	Jane Smith	John Smith	Jane Smith	John Smith	Joint	Joint
13	$113,429	$742,822	$8,321	$64,514	$17,599	$5,238	$4,984	$10,000	$12,000	$500,000	Excess Money to Main Brokerage	Sale of Real Estate
14	$142,246	$801,706	$127,424	$66,856	$18,238	$10,428	$5,165	$10,350	$12,420	$517,500	$118,801	$0
15	$180,994	$911,406	$257,724	$73,307	$19,998	$16,499	$5,663	$10,712	$12,855	$535,613	$118,004	$0
16	$194,097	$1,004,255	$285,121	$79,678	$21,736	$20,071	$6,155	$11,087	$13,305	$554,359	$5,000	$0
17	$110,979	$996,020	$282,783	$79,025	$21,557	$19,907	$6,105	$11,475	$13,770	$573,762	$0	$0
18	$5,299	$1,111,858	$315,670	$88,215	$24,064	$22,222	$6,815	$11,877	$14,252	$593,843	$0	$0
19	-$51,186	$1,154,512	$342,313	$95,660	$26,096	$24,097	$7,390	$12,293	$14,751	$614,628	$0	$0
20	$0	$1,212,727	$377,434	$105,475	$28,773	$26,570	$8,148	$12,723	$15,267	$636,140	$0	$0
21	$0	$1,202,897	$392,909	$105,680	$29,953	$27,659	$8,483	$13,168	$15,802	$658,405	$0	$0
22	$0	$1,192,146	$409,961	$105,987	$31,253	$28,859	$8,851	$13,629	$16,355	$681,449	$0	$0
23	$0	$1,089,080	$419,923	$104,110	$30,791	$29,561	$9,066	$14,106	$16,927	$705,299	$0	$0
24	$0	$1,059,633	$435,167	$103,343	$30,662	$30,634	$9,395	$14,600	$17,520	$729,985	$0	$0
25	$0	$1,089,114	$477,160	$108,618	$32,333	$33,590	$10,301	$15,111	$18,133	$755,534	$0	$0
26	$0	$1,107,401	$518,625	$112,933	$33,731	$36,509	$11,197	$15,640	$18,767	$781,978	$0	$0
27	$0	$1,018,234	$514,373	$106,444	$31,921	$36,209	$11,105	$16,187	$19,424	$809,347	$0	$0
28	$0	$973,568	$574,194	$113,365	$34,128	$40,420	$12,396	$16,753	$20,104	$837,674	$0	$0
29	$0	$967,231	$622,656	$116,870	$35,327	$43,832	$13,443	$17,340	$20,808	$866,993	$0	$0
30	$0	$973,773	$686,541	$122,332	$37,140	$48,329	$14,822	$17,947	$21,536	$897,338	$0	$0
31	$0	$916,835	$714,689	$120,194	$36,676	$50,311	$15,429	$18,575	$22,290	$928,745	$0	$0
32	$0	$854,896	$745,706	$118,036	$36,219	$52,494	$16,099	$19,225	$23,070	$961,251	$0	$0
33	$0	$676,820	$763,827	$113,290	$34,981	$53,770	$16,490	$19,898	$23,877	$994,894	$0	$0
34	$0	$589,162	$791,554	$109,747	$34,105	$55,722	$17,089	$20,594	$24,713	$1,029,716	$0	$0
35	$0	$528,234	$867,939	$112,554	$35,196	$61,099	$18,738	$21,315	$25,578	$1,065,756	$0	$0
36	$0	$451,201	$943,363	$113,936	$35,876	$66,408	$20,366	$22,061	$26,473	$1,103,057	$0	$0
37	$0	$319,135	$935,627	$104,030	$33,038	$65,864	$20,199	$22,833	$27,400	$1,141,664	$0	$0
38	$0	$112,963	$1,044,441	$107,460	$34,414	$73,524	$22,548	$23,632	$28,359	$1,181,622	$0	$0
39	$0	-$18,261	$1,114,330	$107,103	$34,609	$79,729	$24,452	$24,460	$29,352	$1,222,979	$0	$0
40	$0	$0	$1,081,703	$108,175	$35,276	$87,909	$26,960	$25,316	$30,379	$1,265,784	$0	$0
41	$0	$0	$972,882	$102,005	$33,628	$91,513	$28,066	$26,202	$31,442	$1,310,086	$0	$0
42	$0	$0	$854,606	$95,806	$31,974	$95,485	$29,284	$27,119	$32,543	$1,355,939	$0	$0
43	$0	$0	$580,156	$87,606	$29,616	$97,805	$29,995	$28,068	$33,682	$1,403,397	$0	$0
44	$0	$0	$424,707	$80,599	$27,606	$101,356	$31,084	$29,050	$34,860	$1,452,516	$0	$0
45	$0	$0	$280,703	$78,427	$27,236	$111,137	$34,084	$30,067	$36,080	$1,503,354	$0	$0
46	$0	$0	$112,124	$74,923	$26,436	$120,794	$37,046	$31,119	$37,343	$1,555,971	$0	$0
47	$0	$0	-$90,226	-$26,470	-$3,514	$116,290	$36,742	$32,209	$38,650	$1,610,430	$0	$0
48	$0	$0	$0	$0	$0	-$243,688	-$202,673	-$202,673	-$202,673	-$202,673	$0	$1,740,134
49	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
Asset Cessation

Cell Formulas
Range	Formula
AB12:AK12	AB12	=IF(AND(AB6="",AB7="",AB8=""),"-",IF(AB6>0,$AA$6,IF(AB7>0,$AA$7,IF(AB8>0,$AA$8))))
AL14:AL49	AL14	=(IF(AND(Q14<0,R14<Q14),ABS(R14)-ABS(Q14),IF(Q14>0,Q14,0))+(IF(F13<=0,0,IF(Q14>0,0,IF(OR($N$12="",$N$12="No Line"),0,IF(L14<=0,(MAX(ABS(R14),ABS(Q14))),0)))))+(IF(F13<=0,0,IF(Q14>0,0,IF(AND($N$12="Pay Off Every 5",N13<0,N12<0,N11<0,N10<0,N9<0,L14>0,L13>0,L12>0),-(ABS(N13)+(M14*(ABS(N13)))),0)))))+(SUMIF(Investment_Contributions_Tbl[Year],'Asset Cessation'!$A14,Investment_Contributions_Tbl[[Other Contribution/ Year: Client 1]:[Other Contribution/ Year: Client 1]]))+(SUMIF(Investment_Contributions_Tbl[Year],'Asset Cessation'!$A14,Investment_Contributions_Tbl[[Other Contribution/ Year: Client 2]:[Other Contribution/ Year: Client 2]]))
AM14:AM49	AM14	=IF(SUM(AB14:AK14)=0,0,(SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate",AB14:AK14,"<=0")+SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate Primary Res",AB14:AK14,"<=0"))+((SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate",AB14:AK14,"<=0")+SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate Primary Res",AB14:AK14,"<=0"))*$L$2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AB78:AK138,AB14:AK74,L14:Z74	Cell Value	<0	text	NO
AB78:AK138,AB14:AK74,L14:Z74	Cell	contains an error	text	NO

Cells with Data Validation
Cell	Allow	Criteria
AB11:AK11	List	Main Brokerage,Main 401k,Main Roth IRA,Main IRA, Brokerage,401K,Roth IRA,IRA,Real Estate,Real Estate Primary Res

 

[Kscoof]

Use the second posting. I forgot to include the account type in the first one.

 

[StephenCrump]

Thanks for the XL2BB sample. But your formulae include references to a table, and to many other cells, not included in the layout, hence it's hard to know what's going on. It's also not clear how the circular reference is arising, e.g. using the layout in Post #6, if I set

AL14: = AK14

there is no circular reference, although I would have expected one based on your initial description?

Are you able to distil the problem down to a simple example that you can fully attach using XL2BB?

I suspect you have a simple logic error, where the result in row N should refer to cumulative results to row N-1, rather than to row N. Hopefully, if you are able to create a simple example (i.e. eliminating all the other complexity of the workbook) this will be easier to identify.

 

[Kscoof]

Ok I think I simplified the sheet while still demonstrating the problem. See below.

 

[Kscoof]

Net Worth Template_Filing Jointly_KS v11 rev 7.xlsm
	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
11	Main 401k	Main Brokerage	Main 401k	IRA	401K	Main Roth IRA	Main Roth IRA	Real Estate	Real Estate	Real Estate Primary Res
12	Jane Smith	John Smith	John Smith	John Smith	Jane Smith	John Smith	Jane Smith	John Smith	Joint	Joint
13	$113,429	$8,321	$742,822	$64,514	$17,599	$5,238	$4,984	$10,000	$12,000	$500,000	Excess Money to Main Brokerage	Sale of Real Estate
14	$147,131	$246,583	$833,695	$69,634	$18,996	$10,654	$5,380	$10,350	$12,420	$517,500	$118,801	$0
15	$185,648	$375,512	$942,502	$76,021	$20,738	$16,696	$5,873	$10,712	$12,855	$535,613	$118,004	$0
16	$191,424	$279,160	$998,801	$79,462	$21,677	$19,589	$6,139	$11,087	$13,305	$554,359	$5,000	$0
17	$131,067	$304,481	$1,109,263	$88,250	$24,074	$21,756	$6,818	$11,475	$13,770	$573,762	$0	$0
18	$16,480	$313,770	$1,143,106	$90,942	$24,808	$22,420	$7,026	$11,877	$14,252	$593,843	$0	$0
19	-$39,916	$284,075	$1,180,343	$93,904	$25,617	$23,150	$7,255	$12,293	$14,751	$614,628	$0	$0
20	$0	$247,415	$1,278,313	$101,699	$27,743	$25,071	$7,857	$12,723	$15,267	$636,140	$0	$0
21	$0	$249,941	$1,291,880	$102,778	$29,121	$26,317	$8,247	$13,168	$15,802	$658,405	$0	$0
22	$0	$258,305	$1,338,482	$106,486	$31,350	$28,332	$8,878	$13,629	$16,355	$681,449	$0	$0
23	$0	$203,389	$1,390,313	$110,609	$32,657	$30,619	$9,595	$14,106	$16,927	$705,299	$0	$0
24	$0	$215,478	$1,466,302	$116,654	$34,546	$33,630	$10,539	$14,600	$17,520	$729,985	$0	$0
25	$0	$225,302	$1,502,943	$119,570	$35,528	$35,999	$11,281	$15,111	$18,133	$755,534	$0	$0
26	$0	$231,013	$1,502,216	$119,512	$35,635	$37,679	$11,808	$15,640	$18,767	$781,978	$0	$0
27	$0	$234,666	$1,488,867	$118,450	$35,454	$39,201	$12,284	$16,187	$19,424	$809,347	$0	$0
28	$0	$164,686	$1,513,857	$120,438	$36,195	$41,869	$13,121	$16,753	$20,104	$837,674	$0	$0
29	$0	$169,292	$1,540,244	$122,537	$36,978	$44,838	$14,051	$17,340	$20,808	$866,993	$0	$0
30	$0	$176,118	$1,573,145	$125,155	$37,938	$48,300	$15,136	$17,947	$21,536	$897,338	$0	$0
31	$0	$179,057	$1,549,009	$123,234	$37,545	$50,384	$15,789	$18,575	$22,290	$928,745	$0	$0
32	$0	$180,376	$1,521,295	$121,030	$37,080	$52,573	$16,475	$19,225	$23,070	$961,251	$0	$0
33	$0	$88,067	$1,491,881	$118,689	$36,587	$54,949	$17,219	$19,898	$23,877	$994,894	$0	$0
34	$0	$89,120	$1,594,590	$126,861	$39,333	$62,444	$19,568	$20,594	$24,713	$1,029,716	$0	$0
35	$0	$92,955	$1,606,287	$127,791	$39,873	$67,331	$21,100	$21,315	$25,578	$1,065,756	$0	$0
36	$0	$93,876	$1,530,195	$121,738	$38,266	$69,166	$21,675	$22,061	$26,473	$1,103,057	$0	$0
37	$0	$97,056	$1,577,161	$125,474	$39,740	$76,735	$24,047	$22,833	$27,400	$1,141,664	$0	$0
38	$0	-$4,215	$1,576,373	$125,747	$40,172	$83,297	$26,103	$23,632	$28,359	$1,181,622	$0	$0
39	$0	$0	$1,611,734	$128,601	$41,445	$92,494	$28,985	$24,460	$29,352	$1,222,979	$0	$0
40	$0	$9,675	$1,552,812	$123,900	$40,313	$97,677	$30,609	$25,316	$30,379	$1,265,784	$4,837	$0
41	$0	$7,155	$1,470,744	$117,352	$38,599	$102,091	$31,993	$26,202	$31,442	$1,310,086	$1,050	$0
42	$0	$1,301	$1,421,969	$113,460	$37,765	$109,340	$34,264	$27,119	$32,543	$1,355,939	$0	$0
43	$0	-$135,022	$1,267,429	$111,902	$37,694	$119,854	$37,559	$28,068	$33,682	$1,403,397	$0	$0
44	$0	$0	$1,222,136	$110,152	$37,561	$131,916	$41,339	$29,050	$34,860	$1,452,516	$0	$0
45	$0	$0	$1,167,643	$107,887	$37,299	$145,490	$45,593	$30,067	$36,080	$1,503,354	$0	$0
46	$0	$0	$1,046,673	$99,878	$35,100	$153,833	$48,207	$31,119	$37,343	$1,555,971	$0	$0
47	$0	$0	$879,854	$88,676	$31,774	$158,246	$49,590	$32,209	$38,650	$1,610,430	$0	$0
48	$0	$0	$598,133	$82,936	$30,279	$171,621	$53,781	$33,336	$40,003	$1,666,795	$0	$0
49	$0	$0	$450,386	$79,283	$29,487	$191,303	$59,949	$34,503	$41,403	$1,725,133	$0	$0
50	$0	$0	$269,271	$73,693	$28,005	$210,240	$65,884	$35,710	$42,852	$1,785,513	$0	$0
51	$0	$0	$48,270	$64,345	$25,098	$221,795	$69,504	$36,960	$44,352	$1,848,006	$0	$0
52	$0	$0	-$194,030	-$135,153	-$111,615	$133,986	$76,965	$38,254	$45,904	$1,912,686	$0	$0
53	$0	$0	$0	$0	$0	-$306,161	-$223,476	-$223,476	-$223,476	-$223,476	$0	$2,066,734
54	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
55	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
56	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
Asset Cessation

Cell Formulas
Range	Formula
AL14	AL14	=118800.65
AM14:AM56	AM14	=IF(SUM(AB14:AK14)=0,0,(SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate",AB14:AK14,"<=0")+SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate Primary Res",AB14:AK14,"<=0"))+((SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate",AB14:AK14,"<=0")+SUMIFS(AB13:AK13,$AB$11:$AK$11,"Real Estate Primary Res",AB14:AK14,"<=0"))*0.035))
AL15	AL15	=118004.357478844
AL16	AL16	=5000
AL17:AL39,AL42:AL56	AL17	=0
AL40	AL40	=4837.47176959235
AL41	AL41	=1049.65919748903
AC14	AC14	=((IF(AC$11="Main Brokerage",$AL14,0)))+127782.036919444
AC15	AC15	=((IF(AC$11="Main Brokerage",$AL15,0)))+257507.432452291
AC16	AC16	=((IF(AC$11="Main Brokerage",$AL16,0)))+274160.287539338
AC17	AC17	=((IF(AC$11="Main Brokerage",$AL17,0)))+304480.816072845
AC18	AC18	=((IF(AC$11="Main Brokerage",$AL18,0)))+313770.356615219
AC19	AC19	=((IF(AC$11="Main Brokerage",$AL19,0)))+284075.274159129
AC20	AC20	=((IF(AC$11="Main Brokerage",$AL20,0)))+247415.339800805
AC21	AC21	=((IF(AC$11="Main Brokerage",$AL21,0)))+249941.310480112
AC22	AC22	=((IF(AC$11="Main Brokerage",$AL22,0)))+258305.225132697
AC23	AC23	=((IF(AC$11="Main Brokerage",$AL23,0)))+203388.801168945
AC24	AC24	=((IF(AC$11="Main Brokerage",$AL24,0)))+215477.569535077
AC25	AC25	=((IF(AC$11="Main Brokerage",$AL25,0)))+225302.367719717
AC26	AC26	=((IF(AC$11="Main Brokerage",$AL26,0)))+231013.018583122
AC27	AC27	=((IF(AC$11="Main Brokerage",$AL27,0)))+234665.50111623
AC28	AC28	=((IF(AC$11="Main Brokerage",$AL28,0)))+164685.705346819
AC29	AC29	=((IF(AC$11="Main Brokerage",$AL29,0)))+169292.285257299
AC30	AC30	=((IF(AC$11="Main Brokerage",$AL30,0)))+176118.232247436
AC31	AC31	=((IF(AC$11="Main Brokerage",$AL31,0)))+179057.207467699
AC32	AC32	=((IF(AC$11="Main Brokerage",$AL32,0)))+180375.813107312
AC33	AC33	=((IF(AC$11="Main Brokerage",$AL33,0)))+88066.7296947125
AC34	AC34	=((IF(AC$11="Main Brokerage",$AL34,0)))+89120.0389000031
AC35	AC35	=((IF(AC$11="Main Brokerage",$AL35,0)))+92954.7333267923
AC36	AC36	=((IF(AC$11="Main Brokerage",$AL36,0)))+93876.3474529586
AC37	AC37	=((IF(AC$11="Main Brokerage",$AL37,0)))+97055.7169995464
AC38	AC38	=((IF(AC$11="Main Brokerage",$AL38,0)))+-4215.41923914863
AC39,AC44:AC56	AC39	=((IF(AC$11="Main Brokerage",$AL39,0)))+0
AC40	AC40	=((IF(AC$11="Main Brokerage",$AL40,0)))+4837.47176959235
AC41	AC41	=((IF(AC$11="Main Brokerage",$AL41,0)))+6105.72758202523
AC42	AC42	=((IF(AC$11="Main Brokerage",$AL42,0)))+1301.37887619628
AC43	AC43	=((IF(AC$11="Main Brokerage",$AL43,0)))+-135022.33428872

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AB78:AK138,L14:Z74,AB14:AK74	Cell Value	<0	text	NO
AB78:AK138,L14:Z74,AB14:AK74	Cell	contains an error	text	NO

Cells with Data Validation
Cell	Allow	Criteria
AB11:AK11	List	Main Brokerage,Main 401k,Main Roth IRA,Main IRA, Brokerage,401K,Roth IRA,IRA,Real Estate,Real Estate Primary Res