How to avoid Circular reference in a Range

[Projects]

Good Day,

Every one. It is pleasure addressing you all. I am using circular reference to record a value after condition in if formula gets false.

Range is from B2 to E10. Formula is working like if current month and year are identical with Now() then record the value. It will follow the rule up till the last date of the month. When new month starts, the last value will be retained because of circular reference. I would like to replace circular reference with VBA code. Any help?

formula in B2 is =IF(AND(MONTH($A2)=MONTH($G$2), YEAR($A2)=YEAR($G$2)), $F$2,$B2). $B2 is a circular reference.

[SamT]

What should the formula return when the date in A <> $G$2?

[Projects]

It will return the value of B2. i.e if the value in $F$2 is 20 Then it will appear in B2 in case condition is true otherwise formula will retain the value of B2 itself i.e. 20. F2 is changing so keep the record of different values of F2 over period of time. This formula works fine but circular reference message is appearing so i would like to resolve it through VBA code.

Regards,

[SamT]

It will return the value of B2
I know that.

Where and when did that value get in B2?

I can't hep you until I know what is happening over time

Tell me using Cell addresses and dates.

What is the full range that formula is in, from B2 to B??

[Projects]

It will return the value of B2. i.e if the value in $F$2 is 20 Then it will appear in B2 in case condition is true otherwise formula will retain the value of B2 itself i.e. 20. F2 is changing so keep the record of different values of F2 over period of time. This formula works fine but circular reference message is appearing so i would like to resolve it through VBA code.

Regards,



File is attached for your review. It is to get and store values in respective column. Values give in F2, G2 and H2 are sum of values. only for display. the purpose is all these three values should record in cell B2, C2 and D2 during January and on 1st day of Feb. the value in B2, C2 and D2 store the last value displayed i that cell with the help of Circular reference and now values should be stored in B3, C3 and D3 respectively





15298

[Paul_Hossler]

File is attached for your review

Actually that's a screen shot.

Instead of making us generate a workbook to look at a possible solution, it'd be a lot easier if you attached a workbook with some data and a explanation with before and afters

Click [Advanced] and then the 'paperclip' icon to attach one

[SamT]

Test this for me and see if it works.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long

If Not Target Is Range("J2") Then Exit Sub
rw = 2 + DateDiff("m", Range("A2"), Target)
Range("B" & rw & ":D" & rw) = Range("F2:H2")

End Sub