Circular Reference Error

VinceF

VinceF

Board Regular
Joined
Sep 22, 2007
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
I hope that I can explain this better than the last attempt.
I've tried to move data around and also tried helper cells...nothing worked and I keep getting the circular error message.
Right now I have to manually input the adjusted number in the appropriate cell, I'm hoping there's a VBA code to accomplish this.
This is a golf game that tracks the players results and adjust accordingly.

"MAIN" sheet cell AL7 will be where the command button will reside to execute the VBA code

"PLAYER QUOTA HISTORY" sheet
E2 is the Round# that is being played, this number will be between 1 and 40
B5:B141 show the players results from the round pulled from the "MAIN" sheet
G5:G141 is the players adjusted quota
H5:H141 is where the adjusted quota number would go for ROUND 1
I5:I141 is where the adjusted quota number would go for ROUND 2 and so forth until ROUND 40
F2 If a player is over quota you add to his current adjusted quota number, under quota you subtract from his current adjusted quota number
Sorry about Column F2 for being so wide...not sure why it did it when I copied the mini table using XL2bb

So in essence, I'd like to have the number in G5:G141 adjusted according to the schedule in F2 and reported in the Round# that was played.


Example from the sheet below, ROUND 1
Adam Altman shows +8 in cell B5 His adjusted quota # in cell G5 is 28 according to the schedule in F2 since he was over quota the adjusted number to go into cell H5 would be +2 points or "30" this becomes his new current adjustment quota number for the next round that will be played.
Anthony Stefani shows -10 in cell B5 His adjusted quota # in cell G5 is 27 according to the schedule in F2 since he was under quota the adjusted number to go into cell H5 would be -3 points or "24"

I'd appreciate any assistance that you can offer.
VinceF
Excel 2016

1-20 INDIANWOOD QUOTA - SKINS.xlsm
BCDEFGHIJK
2MAIN PAGEINSTRUCTIONS TO ADD PLAYER1 ADJUST ACCORDING TO "POINTS" B5:B141 ( -2 TO +2 ) NO ADJUSTMENT ( -3 TO +6 ) +1 OR -1 POINT ( -7 TO +9 ) +2 OR -2 POINTS ( -10 TO +10 ) OR MORE +3 OR -3 POINTS
3CURRENTSTARTINGADJUSTEDROUNDROUNDROUNDROUND
4POINTS ROUNDPLAYERHDCPQUOTAQUOTA1234
5+872ADAM ALTMAN82828
6  ADAM PIOCH   
7  ALLEN PRAET   
8-1091ANTHONY STEFANI92727
9+380AUSTIN ROMZEK122424
10  BILL KNIGHT   
11-485BILLY UHL102626
12  BRANDON LESKE   
13+1076BRIAN KNOX142222
14  CHARLIE SCHULTZ   
15  CHRIS KIPP   
Player Quota History
Cell Formulas
RangeFormula
B5:B15B5=IFERROR(INDEX(Main!$AK$11:$AK$50,MATCH($D5,Main!$G$11:$G$50,0)),"")
C5:C15C5=IFERROR(INDEX(Main!$AI$11:$AI$50,MATCH(D5,Main!$G$11:$G$50,0)),"")
E5:E15E5=IFERROR(INDEX(Main!$I$11:$I$50,MATCH(D5,Main!$G$11:$G$50,0)),"")
F5:F15F5=IFERROR(INDEX(Main!$J$11:$J$50,MATCH(D5,Main!$G$11:$G$50,0)),"")
G5:G15G5=IFERROR(IF(AND($E$2=1,H5=""),F5,LOOKUP(2,1/($H5:$AK5<>""),$H5:$AK5)),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D6:D141Cell Valuecontains "ZZZ"textNO
K5Expression=AND(ISNUMBER($B5),$E$2=4)textNO
I5Expression=AND(ISNUMBER($B5),$E$2=2)textNO
H5Expression=AND(ISNUMBER(B5),$E$2=1)textNO
J5Expression=AND(ISNUMBER($B5),$E$2=3)textNO
B5Expression=ISNUMBER(B5)textNO
C5Expression=ISNUMBER(C5)textNO
D6:D141Expression=ISNUMBER(B6)textNO
G5:G141Expression=ISTEXT(D5)textNO
F6:F141Expression=ISTEXT(D6)textNO
F5Expression=ISTEXT(D5)textNO
E6:E141Expression=ISTEXT(D6)textNO
E5Expression=ISTEXT(D5)textNO
D5Expression=ISNUMBER(B5)textNO
H6:H141Expression=AND(ISNUMBER(B6),$E$2=1)textNO
K6:K141Expression=AND(ISNUMBER($B6),$E$2=4)textNO
J6:J141Expression=AND(ISNUMBER($B6),$E$2=3)textNO
I6:I141Expression=AND(ISNUMBER($B6),$E$2=2)textNO
B6:C141Expression=ISNUMBER($B6)textNO
D4Expression=ISNUMBER(#REF!)textNO
H4Expression=AND(ISNUMBER(#REF!),$E$2=1)textNO
I4Expression=AND(ISNUMBER(#REF!),$E$2=2)textNO
J4Expression=AND(ISNUMBER(#REF!),$E$2=3)textNO
K4Expression=AND(ISNUMBER(#REF!),$E$2=4)textNO
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
55 views without a suggestion, I'm assuming that my inability to properly explain what is needed is the problem.
Would someone please let me know if there's something I can to do to better explain the situation.

To condense it...
I'd like to have a command button on the "MAIN" sheet that adjusts the numbers on the "Player Quota History" sheet in G5:G141 based on the numbers in B5:B141 according to the schedule in F2 and report that number into H5:H141


Thanks,
VinceF
Excel 2016
 
Last edited:
Upvote 0
Try;
VBA Code: 
Sub AdjustNumber()
    Application.ScreenUpdating = False
    Dim round As Range, points As Range, desWS As Worksheet
    Set desWS = Sheets("PLAYER QUOTA HISTORY")
    With desWS
        Set round = .Range("H4:K4").Find(.Range("E2").Value, LookIn:=xlValues, lookat:=xlWhole)
        For Each points In .Range("B5", .Range("B" & .Rows.Count).End(xlUp))
            If points <> "" Then
                Select Case True
                    Case points.Value >= -2 And points.Value <= 2
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value
                    Case points.Value >= -3 And points.Value <= 6
                        If points.Value < 0 Then
                            .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 1
                        Else
                            .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 1
                        End If
                    Case points.Value >= -7 And points.Value <= 9
                        If points.Value < 0 Then
                            .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 2
                        Else
                            .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 2
                        End If
                    Case points.Value >= -10 And points.Value <= 10
                        If points.Value < 0 Then
                            .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 3
                        Else
                            .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 3
                        End If
                End Select
            End If
        Next points
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Mumps...Thanks so much for the reply.
It worked perfectly for Round#1 but when I did Round #2 I received rutime error 13 and/or the debug error for this line <<<< >>>>>>

Sub AdjustNumber()
Application.ScreenUpdating = False
Dim round As Range, points As Range, desWS As Worksheet
Set desWS = Sheets("PLAYER QUOTA HISTORY")
With desWS
Set round = .Range("H4:K4").Find(.Range("E2").Value, LookIn:=xlValues, lookat:=xlWhole)
For Each points In .Range("B5", .Range("B" & .Rows.Count).End(xlUp))
If points <> "" Then
Select Case True
Case points.Value >= -2 And points.Value <= 2
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value
Case points.Value >= -3 And points.Value <= 6
If points.Value < 0 Then
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 1
Else
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 1
End If
Case points.Value >= -7 And points.Value <= 9
If points.Value < 0 Then
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 2
Else
>>>>> >>>> .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 2 <<<<<<<<<<<<<<<<<<
End If
Case points.Value >= -10 And points.Value <= 10
If points.Value < 0 Then
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 3
Else
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 3
End If
End Select
End If
Next points
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
The macro assumes that your data starts in row 2 as in the data you posted. Are you using the macro on that same data or does your data start on a different row?
 
Upvote 0
Mumps,
After round 1 was completed and going into round 2, I believe that it was looking for a number in column G. If it's the players 1st time playing there wouldn't be an adjusted quota number. So I changed a formula so that column G showed a quota number from the main page and it's working perfectly now.
I really, really appreciate your help on this....I marked it as solved.

Thank You...!
VinceF
 
Upvote 0
Mumps was kind enough to resolve my original post. However I made a change and now I cant seem to have it adjust as needed.
Is it possible that you could adjust the code that you provided to conform to the following requirements? Very much appreciated.

What I need is to adjust according to the following schedule...
-10 or more = -3
-9 = -2
-8 = -2
-7 = -2
-6 = -1
-5 = -1
-4 = -1
-3 = -1
-2 = 0
-1 = 0
0 = 0
+1 = 0
+2 = 0
+3 = +1
+4 = +1
+5 = +1
+6 = +1
+7 = +2
+8 = +2
+9 = +2
+10 or more = +3

The code that you provided was...

Sub AdjustNumber()
Application.ScreenUpdating = False
Dim round As Range, points As Range, desWS As Worksheet
Set desWS = Sheets("PLAYER QUOTA HISTORY")
With desWS
Set round = .Range("H4:K4").Find(.Range("E2").Value, LookIn:=xlValues, lookat:=xlWhole)
For Each points In .Range("B5", .Range("B" & .Rows.Count).End(xlUp))
If points <> "" Then
Select Case True
Case points.Value >= -2 And points.Value <= 2
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value
Case points.Value >= -3 And points.Value <= 6
If points.Value < 0 Then
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 1
Else
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 1
End If
Case points.Value >= -7 And points.Value <= 9
If points.Value < 0 Then
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 2
Else
>>>>> >>>> .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 2 <<<<<<<<<<<<<<<<<<
End If
Case points.Value >= -10 And points.Value <= 10
If points.Value < 0 Then
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 3
Else
.Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 3
End If
End Select
End If
Next points
End With
Application.ScreenUpdating = True
End Sub

Very much appreciated if you can help...

VinceF
Excel 201
 
Upvote 0
Try:
VBA Code: 
Sub AdjustNumber()
    Application.ScreenUpdating = False
    Dim round As Range, points As Range, desWS As Worksheet
    Set desWS = Sheets("PLAYER QUOTA HISTORY")
    With desWS
        Set round = .Range("H4:K4").Find(.Range("E2").Value, LookIn:=xlValues, lookat:=xlWhole)
        For Each points In .Range("B5", .Range("B" & .Rows.Count).End(xlUp))
            If points <> "" Then
                Select Case True
                    Case points.Value <= -10
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 3
                    Case points.Value >= -9 And points.Value <= -7
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 2
                    Case points.Value >= -6 And points.Value <= -3
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value - 1
                    Case points.Value >= -2 And points.Value <= 2
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value
                    Case points.Value >= 3 And points.Value <= 6
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 1
                    Case points.Value >= 7 And points.Value <= 9
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 2
                    Case points.Value >= 10
                        .Cells(points.Row, round.Column) = .Range("G" & points.Row).Value + 3
                End Select
            End If
        Next points
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Thank you Mumps, it's working perfectly.
VinceF
 
Upvote 0
You must log in or register to reply here.

Similar threads

VinceF
  • Question
VBA To Copy & Paste Value
Replies
0
Views
157
VinceF
VinceF
VinceF
  • Question
Circular Reference Error
Replies
3
Views
220
VinceF
VinceF
VinceF
  • Solved
Copy/Paste Value Only If The Cell Contains A Number
Replies
9
Views
201
smozgur
smozgur
VinceF
  • Question
Circular Reference Error
Replies
8
Views
391
VinceF
VinceF
I
  • Solved
Simple Countif producing wrong results
Replies
4
Views
61
Cubist
Cubist

Forum statistics

Threads
1,215,073
Messages
6,122,976
Members
449,095
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top