Circular references

[blaisjoel]

I have been scratching my head for months now with this issue.
Here is my main sheet with all units info :

[TABLE="width: 500"]
<tbody>[TR]
[TD]UNIT INFO[/TD]
[TD]MAINTENANCE TYPE[/TD]
[/TR]
[TR]
[TD]228-12[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]228-17[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]336-10[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]336-15[/TD]
[TD]1500[/TD]
[/TR]
</tbody>[/TABLE]

Here is my PM sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]UNIT[/TD]
[TD]MAINTENANCE TYPE[/TD]
[TD]DUE IN[/TD]
[/TR]
[TR]
[TD]228-12[/TD]
[TD]1500[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]228-17[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]336-10[/TD]
[TD]500[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]336-15[/TD]
[TD]1500[/TD]
[TD]1500[/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to say "If the cell in PM sheet, column DUE IN is = to PM sheet column MAINTENANCE TYPE, then Main sheet Column MAINTENANCE TYPE = PM sheet column DUE IN + 250.

In other words, I want to be able to use a vlookup and add 250 to the value in the Main sheet for column "MAINTENANCE TYPE" when, in PM sheet, column "DUE IN is equal to column "MAINTENANCE TYPE in PM sheet.

The problem with all this is that the column "MAINTENANCE TYPE" in PM sheet uses a vlookup formula to get it's value from the column "MAINTENANCE TYPE" in the Main sheet.

Every method I tried keeps giving me a circular reference.

 

[FormR]

Hi, is it this maybe..:

Excel 2013/2016
	A	B
1	UNIT INFO	MAINTENANCE TYPE
2	228-12	1500
3	228-17	1250
4	336-10	500
5	336-15	1750
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,PM!A:C,2,0)+IF(VLOOKUP(A2,PM!A:C,2,0)=VLOOKUP(A2,PM!A:C,3,0),250,0)

Excel 2013/2016
	A	B	C
1	UNIT	MAINTENANCE TYPE	DUE IN
2	228-12	1500	43
3	228-17	1000	1000
4	336-10	500	1
5	336-15	1500	1500
PM

 

[wideboydixon]

Indeed you can't do what you're trying to do due to the circular reference. You're trying to implement these conditions:

{PM Sheet Maintenance Type} depends on {Main Sheet Maintenance Type} - based on VLOOKUP
{Main Sheet Maintenance Type} depends on ({PM Sheet Maintenance Type} and {PM Sheet Due In})

You're solution here is to use a macro to update the Main Sheet based on the values in the PM Sheet. Easiest is probably to use a macro which triggers when values in the "DUE IN" column change on PM sheet. Here's what I did:

Book1
	A	B
1	UNIT	MAINTENANCE TYPE
2	228-12	1500
3	228-17	1000
4	336-10	500
5	336-15	1500
Main

Book1
	A	B	C
1	UNIT	MAINTENANCE TYPE	DUE IN
2	228-12	1500	43
3	228-17	1000	999
4	336-10	500	1
5	336-15	1500	1499
PM

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,Main!$A:$B,2,FALSE)

Right click "PM" sheet, select View Code and paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim checkRange As Range
Dim checkCell As Range
Dim foundRow As Variant

Set checkRange = Application.Intersect(Target, Range("C:C"))
If checkRange Is Nothing Then Exit Sub

For Each checkCell In checkRange
    If checkCell.Value = checkCell.Offset(0, -1).Value Then
        foundRow = Application.Match(checkCell.Offset(0, -2).Value, Sheets("Main").Range("A:A"), 0)
        If Not IsError(foundRow) Then
            Sheets("Main").Cells(foundRow, 2).Value = Sheets("Main").Cells(foundRow, 2).Value + 250
        End If
    End If
Next checkCell

End Sub

WBD