Static Copy of Cell Result

[MrDB4Excel]

When a cell gets a result because of conditional formatting how to copy that cell’s result to another location as a static copy.

The attached Xl2bb Mini Sheets are as follows:

ProjectedSavings: This sheet is a bogus copy of another sheet I use to gather data from other workbooks, but here only shows some bogus numbers to facilitate an intended outcome.

ProjectedMonthTotals: This sheet seeks to record the “Projected Total Available for New Home Purchase by the End of 2022” for each month. My intent is for each record to become static when copied to the relevant month’s cell in column B “Approximate Total $ Saved by End of Year”. In either sheet, as indicated by the attached image showing conditional formatting, the specific cell in each sheet that shows a result of the formula within such cell when the two dates are equal is what I want to be copied to the relevant month in column B in sheet ProjectedMonthTotals.

Obviously, I could do this manually but was hoping there is a method to automate this process so that at the end of any given month when the conditional formatting displays the intended result then that result gets copied statically to the relevant month in column B of sheet ProjectedMonthTotals.

BogusCellResultCopy.xlsm
	E	F	G	H	I	J	K
1		4/28/2022	10:42:01
2
3		Current Account Balance
4		$1,000.00			Months Left of 2022 After Current Pension Month	8
5					Current Savings Status	$10,000.00
6	1/1/2022	4			Projected Monthly Savings From Pension After Expenses	$1,000.00
7	5/1/2022	117			Projected Savings From Pension Through To Year's End	$8,000.00
8	4/30/2022				Current Bank Balance	$1,000.00
9		=IF($A20=2,$D$1,"")			Total Projected Savings	$19,000.00
10		4/28/2022 10:42			Projected Sale of Current Home	$100,000.00
11		Current Time ððð	10:42:01		Projected Total Available for New Home Purchase by the End of 2022	$119,000.00	ProjectedMonthTotals
12	Today	4/28/2022				2022	ï Year
13	B4Today	4/27/2022			Last Day of Month Get Projected Total & Paste in Appropriate Month in ProjectedMonthTotals Sheet	4	ï Month
14	After Today	4/29/2022
15	Current Month	April				2022	2022: Current Year's Day Number Is 118
16						118
17						365	247 Days Remaining in 2022
18
19						247
ProjectedSavings

Cell Formulas
Range	Formula
F1,F12	F1	=TODAY()
G1,F10	G1	=NOW()
I4	I4	="Months Left of "&J12&" After Current Pension Month"
J4	J4	=12-MONTH(F12)
F6	F6	=DATEDIF(E6,E7,"m")
F7	F7	=ABS(IF(ISBLANK(E6),"",TODAY()-E6))
E7	E7	=EOMONTH(F1,0)+1
E8	E8	=EOMONTH(F1,0)
J7	J7	=SUM(J6*J4)
J8	J8	=F4
J9	J9	=SUM(J5,J7:J8)
G11	G11	=TIME(HOUR(F10),MINUTE(F10), SECOND(F10))
I11	I11	="Projected Total Available for New Home Purchase by the End of "&J12
J11	J11	=SUM(J9:J10)
F13	F13	=SUM(F12-1)
F14	F14	=SUM(F12+1)
F15	F15	=TEXT(TODAY(),"MMMM")
J15	J15	=YEAR(TODAY())
K15	K15	=J15&": Current Year's Day Number Is "&J16
I16	I16	=IF(F12=E8, J11, "")
J16	J16	=TODAY()-DATE(YEAR(TODAY()),1,0)
J17	J17	=DATE(YEAR(F1),12,31)-DATE(YEAR(F1),1,1)+1
K17	K17	=J19&" Days Remaining in "&J15
J19	J19	=SUM(J17-J16)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I16	Expression	=I16=J11	text	NO
A1:E1	Expression	=MOD(ROW(),2)=0	text	NO

BogusCellResultCopy.xlsm
	A	B	C	D	E	F	G	H	I
1	Projected Total Available @ the End of Year Indicated for New Home Purchase Per Month/Year Indicated	Approximate Total $ Saved by End of Year	Months Remaining		Year
2	Per April / 2022 With 8 Months Remaining		8		2022	January
3	Per May / 2022 With 7 Months Remaining		7		2023	February
4	Per June / 2022 With 6 Months Remaining		6		2024	March
5	Per July / 2022 With 5 Months Remaining		5			April
6	Per August / 2022 With 4 Months Remaining		4			May
7	Per September / 2022 With 3 Months Remaining		3			June
8	Per October / 2022 With 2 Months Remaining		2			July
9	Per November / 2022 With 1 Months Remaining		1			August
10	Per December / 2022 With 0 Months Remaining		0			September
11	Per January / 2023 With 11 Months Remaining		11			October
12	Per February / 2023 With 10 Months Remaining		10			November
13	Per March / 2023 With 9 Months Remaining		9			December
14	Per April / 2023 With 8 Months Remaining		8						$119,000.00
15	Per May / 2023 With 7 Months Remaining		7					Today	4/28/2022
16	Per June / 2023 With 6 Months Remaining		6						4/30/2022
17	Per July / 2023 With 5 Months Remaining		5
18	Per August / 2023 With 4 Months Remaining		4
19	Per September / 2023 With 3 Months Remaining		3
20	Per October / 2023 With 2 Months Remaining		2
21	Per November / 2023 With 1 Months Remaining		1
22	Per December / 2023 With 0 Months Remaining		0
ProjectedMonthTotals

Cell Formulas
Range	Formula
C12:C22,C3:C10	C3	=SUM(C2-1)
H14	H14	=IF($I$15=$I$16,$I$14,"")
I14	I14	=ProjectedSavings!J11
I15	I15	=TODAY()
I16	I16	=EOMONTH(I15,0)
A2:A10	A2	="Per "&F5&" / "&$E$2&" With "&C2&" Months Remaining"
A11:A22	A11	="Per "&F2&" / "&$E$3&" With "&C11&" Months Remaining"

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H14	Expression	=H14=I14	text	NO

 

[sijpie]

So if I understand correctly, when you would open the sheet on the last day of the month, then a macro would take the value from sheet ProjectSavings!J11 and put it into ProjectedMonthSavings!Bx

You can program that in the workbook code-module, using the Workbook_Open() event.

Need help with that?

 

[MrDB4Excel]

So if I understand correctly, when you would open the sheet on the last day of the month, then a macro would take the value from sheet ProjectSavings!J11 and put it into ProjectedMonthSavings!Bx

You can program that in the workbook code-module, using the Workbook_Open() event.

Need help with that?

Well sijpie, I have since come up with the following code which seems to work well. Being that this workbook is the second file I open and leave open all day which contains links to many other workbooks I work on during the day then having a Workbook_Open() event seems different, but share it anyway if you like. I will surely learn something new.
Many thanks

Sub ApproxTotalUpdate()
'
' ApproxTotalUpdate Macro
' Updates End of Month Approximate Total Savings
ActiveSheet.Range("J33").Select
Selection.Copy
Sheets("ProjectedMonthTotals").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(1, 0).Select
End Sub

However, I use this in conjunction with this formula: =IF(F34=E30, J33, "") and conditional formatting to highlight the cell this formula resides in when =I38=J33 and applied to I38.
These cell references are different than the bogus Xl2bb Mini Sheets I posted because I am doing this in the worksheet that the bogus sheet was tailored upon.