Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Thanked 0 Times in 0 Posts

    Purposeful Circular Reference, resulting in 0 when linked, anyway to avoid?

    Hi all,

    I have a spreadsheet that will be updated weekly by another user.

    On the first tab are the questions the user must answer, on the second tab is a schedule that is used to populate some areas on the first tab.

    On the first sheet (Data Sheet) in cell B9 the user enters a quantity 1-7, in cell B10 the user enters a date
    On the second tab is a schedule, in column K are quantity 1-7 in cells K4:K10,
    On the second tab I have this formula in L4:L10
    =IF(K4='Data Sheet'!$B$9,'Data Sheet'!$B$10,L4)

    The intention that as the information on the Data Sheet in B9 changes from week to week, the date populated in Data Sheet B10 is only moved to L4 if the information matches K4, if not it stays the previous week's information and the next line down (with the formula) picks up the new date.

    I then need to be able to use the result in L4 in another cell, let's say for this exercise N4.

    The formula works as I am intending, if the data matches it brings over the date in H10, if it doesn't match the previous entry remains in L4 and the new information moves to L5. What doesn't work is linking to L4. A zero value is returned in N4.

    I am trying to make it so the user only has a few cells to deal with rather than 10 or more lines in some cases. And I need to be able to link to the results and for the results to be the previous date entry not 0.

    Is there any way to do this? Or because of the nature of circular references am I out of luck?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Pa, USA
    Thanked 800 Times in 729 Posts

    What I think you are tryng to do can be done with vba. Changing the value of B9 (1-7) on the Data Sheet will initiate the code to start looking at K4 on sheet2. If it equals B9 from sheet 1 then the date from B10 will be carry over to L4. If it is not the same then K5 will be evaluated to place the date in L5 or not. The evaluation moves down the K column to K10 looking for a match to place the date in the col L. In the process, no dates will be over written. The dates in col L will remain when new data is added and will be values not formulas


    Place code in the Data Sheet worksheet module:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B9")) Is Nothing Then
        Dim ws1 As Worksheet, ws2 As Worksheet
        Set ws1 = Worksheets("Data Sheet")
        Set ws2 = Worksheets("Sheet2")
        For I = 4 To 10
            If ws1.Range("B9") = ws2.Range("K" & I) And ws2.Range("L" & I) = "" Then
                ws2.Range("L" & I) = ws1.Range("B10")
                Exit Sub
            End If
        Next I
    End If
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2015-03-21 at 07:16.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Risri (2015-03-23)

  4. #3
    New Lounger
    Join Date
    Mar 2015
    Thanked 0 Times in 0 Posts
    Thanks Maudibe!

    I will try this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts