Avoiding circular reference - formula based approach vs. VBA?

[skitheast13]

Hello All,

Some of the deals in the table below will only provide either the financial statements or the quarterly report. Some deals will provide both.
For those deals that only provide one type, I would like to reflect the date the report is provided in both financials/reports columns.

In other words, on sheet 1, if cell in column B = yes, cells in columns C & D should reflect the same date even if only one of the types of reports has been received.
However, if cell in column B is blank, the cells should be able to populate dates individually if both types of reports are received.

I wanted to use an isnumber formula to check if there was a date in either cell already, and to just populate that date if there already is one. But obviously referencing the opposite cell is causing a circular reference. Is there a way to do this formulaically?

Sheet 1, summary with lookups up to sheet 2:

Deal	Receives Only One Report	Financials Received Date	Report Received Date
Greyes	Yes	4/17/2023​
Redwood	Yes		5/28/2023​
Rocket	Yes		5/29/2023​
Recon		6/30/2023​	6/30/2023​
Tinted	Yes	8/10/2023​

Sheet 2, data sheet with dates:

Deal	Financials Received Date	Report Received Date
Greyes	4/17/2023​
Redwood		5/28/2023​
Rocket		5/29/2023​
Recon	6/30/2023​	6/30/2023​
Tinted	8/10/2023​

Thank you for your help!

 

[Micron]

AFAIK, the only way to beat the circular reference would be to use the SheetChange event for sheet2. However, an unknown is, is the target cell location always in the same row as the source? It is in what you show, so using logic like this in the SheetChange event:
- if the target column value is 2, Target.Column property+1 and +2 defines the columns to copy the values to Sheet1 in the same row.
- if target column value is 3, copy to Target.Column property value +1 and -1 to Sheet1 in the same row
- if target column is not 2 and is not 3, then do nothing.
Does that sound right? You could also wait to see if a formula guru posts a formula.

 

[Micron]

Edit to the above: I forgot this rule

if cell in column B is blank, the cells should be able to populate dates individually if both types of reports are received

Not sure if that means don't copy over because it will be done manually on sheet1 or what. However, it does mean that the value in B has to be considered as part of the IF logic.

 

[kevin9999]

Does this do what you want?

Book1
	A	B	C	D
1	Deal	Receives Only One Report	Financials Received Date	Report Received Date
2	Greyes	Yes	4/17/2023	4/17/2023
3	Redwood	Yes	5/28/2023	5/28/2023
4	Rocket	Yes	5/29/2023	5/29/2023
5	Recon		6/30/2023	6/30/2023
6	Tinted	Yes	8/10/2023	8/10/2023
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=IF(B2<>"",IF(VLOOKUP($A2,Sheet2!$A$2:$C$6,2,0)="",VLOOKUP($A2,Sheet2!$A$2:$C$6,3,0),VLOOKUP($A2,Sheet2!$A$2:$C$6,2,0)),VLOOKUP($A2,Sheet2!$A$2:$C$6,2,0))
D2:D6	D2	=IF(C2<>"",IF(VLOOKUP($A2,Sheet2!$A$2:$C$6,2,0)="",VLOOKUP($A2,Sheet2!$A$2:$C$6,3,0),VLOOKUP($A2,Sheet2!$A$2:$C$6,2,0)),VLOOKUP($A2,Sheet2!$A$2:$C$6,3,0))

 

[Micron]

I marvel at those who can do that because I can't, but if something changes, which is easier for most people to maintain or modify - a formula like that or code like this?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 2 And Target.Column <> 3 Then Exit Sub
If Target.Column = 2 Then
    With Sheets("Sheet3")
        .Cells(Target.Row, Target.Column + 1) = Target
        .Cells(Target.Row, Target.Column + 2) = Target
    End With
End If
If Target.Column = 3 Then
    With Sheets("Sheet3")
        .Cells(Target.Row, Target.Column) = Target
        .Cells(Target.Row, Target.Column + 1) = Target
    End With
End If

End Sub

I'm asking because I don't know. To me, the code slightly resembles how we speak/think so I tend to think it is the thing that the user might successfully modify when needed. Maybe it's just because I can't do complex formulas (and frankly, feel like it's too late to get good at it). Someone who's a wizard at formulas might have the exact same outlook as me, but from the opposite side of things.
N.B I consider that code to be incomplete because of unanswered questions from my prior posts.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Avoiding circular reference - formula based approach vs. VBA?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[skitheast13]

Does this do what you want?

Book1
	A	B	C	D
1	Deal	Receives Only One Report	Financials Received Date	Report Received Date
2	Greyes	Yes	4/17/2023	4/17/2023
3	Redwood	Yes	5/28/2023	5/28/2023
4	Rocket	Yes	5/29/2023	5/29/2023
5	Recon		6/30/2023	6/30/2023
6	Tinted	Yes	8/10/2023	8/10/2023
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=IF(B2<>"",IF(VLOOKUP($A2,Sheet2!$A$2:$C$6,2,0)="",VLOOKUP($A2,Sheet2!$A$2:$C$6,3,0),VLOOKUP($A2,Sheet2!$A$2:$C$6,2,0)),VLOOKUP($A2,Sheet2!$A$2:$C$6,2,0))
D2:D6	D2	=IF(C2<>"",IF(VLOOKUP($A2,Sheet2!$A$2:$C$6,2,0)="",VLOOKUP($A2,Sheet2!$A$2:$C$6,3,0),VLOOKUP($A2,Sheet2!$A$2:$C$6,2,0)),VLOOKUP($A2,Sheet2!$A$2:$C$6,3,0))

Thanks for your help - this is as close as I have gotten, but it seems to ignore the attribute in column B. It will display a date in both cells regardless of the cell in column B's contents. Any thoughts?

 

[skitheast13]

AFAIK, the only way to beat the circular reference would be to use the SheetChange event for sheet2. However, an unknown is, is the target cell location always in the same row as the source? It is in what you show, so using logic like this in the SheetChange event:
- if the target column value is 2, Target.Column property+1 and +2 defines the columns to copy the values to Sheet1 in the same row.
- if target column value is 3, copy to Target.Column property value +1 and -1 to Sheet1 in the same row
- if target column is not 2 and is not 3, then do nothing.
Does that sound right? You could also wait to see if a formula guru posts a formula.

Thanks for your response. Unfortunately, the data in sheet 2 will be sorted randomly.

 

[kevin9999]

It will display a date in both cells regardless of the cell in column B's contents. Any thoughts?

I thought that was what you wanted. The only column B cell in your original post was the Recon line - but that had dates in both the Financials and Reports columns on sheet 2 - which is why it was returning both dates? I'm obviously not following the logic correctly, although I'm convinced that a formula solution will be possible. It would be easier if you could show me your expected outcomes. Please complete the table below with what you're expecting & post the result using the XL2BB add in.

Book1
	A	B	C	D
1	What should these dates be…
2	Deal	Receives Only One Report	Financials Received Date	Report Received Date
3	Greyes	Yes
4	Redwood	Yes
5	Rocket	Yes
6	Recon
7	Tinted	Yes
8
9
10	If these are the values on sheet 2?
11	Deal	Financials Received Date	Report Received Date
12	Greyes	4/17/2023
13	Redwood		5/28/2023
14	Rocket		5/29/2023
15	Recon	6/30/2023	6/30/2023
16	Tinted	8/10/2023
Sheet1

 

[skitheast13]

I thought that was what you wanted. The only column B cell in your original post was the Recon line - but that had dates in both the Financials and Reports columns on sheet 2 - which is why it was returning both dates?

Apologies for any confusion. Column b exists as some deals provide both types of reports. We want to be able to distinguish between the receipt dates of those types of reports.

Deal 'Recon' might receive the financials on 6/30/2023 but could receive the report on 7/15/2023. Both of these dates should be able to pull through.

The formula provided works if we need the same date to be reflected for both report types for all deals. However, some deals will provide both types of reports, potentially on different dates and will be marked by a blank cell in column B. For deals with a blank cell in column B, cells in columns C and D should look up to the data tab separately and not report the same value. When I tested your formula with the deal 'Recon', it was not able to pull in both dates.

Let me know if this explanation makes more sense. The outcome you had is correct, but there was context/different data lacking from my original request.