Circular Reference Error

[VinceF]

Greetings,
Tough one for me to explain, hopefully you can understand the situation.
I've tried helper cells, moving data around...all to no avail.
Need a formula or VBA to resolve this. (VBA skills limited to cut n' paste)

Cells B5:B141 are extracted from the Main sheet and represent how much the player was under or over their quota.
Cells H5:H141 or "ROUND 1" were manually inputted (this is the part that I'm trying to automate)
Cells G5:G141 is the players adjusted quota. This number may or may not change after each round based on their performance. You'll see that G5:G141 simply reports the last number in the row. So as each round is played there will be a new quota number.
In Cell F2 is the schedule that based on performance is used to adjust their next quota number.
For Example:
Adam Pioch was -8 (8 shots under his quota) so the 35 in cell G6 would be reduced by 3 to 32 by reporting the number in cell I6
Anthony Stefani was +4 (4 shots over his quota) so the 30 in cell G8 would be increased by 1 to 31 by reporting the number in cell I8

If you suggest a formula in the cell to achieve the desired results I realize that I may have to "copy & paste value" to preserve the results but I'm hoping not.

Thanks very much for any assistance.
Vince F
Excel 2016

test INDIANWOOD QUOTA - SKINS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1
2		MAIN PAGE		INSTRUCTIONS	2	( -2 or +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
3					STARTING	STARTING	ADJUSTED	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND	ROUND
4		POINTS	ROUND	PLAYER	HDCP	QUOTA	QUOTA	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
5				ADAM ALTMAN
6		-8	82	ADAM PIOCH	3	33	35	35
7				ALLEN PRAET
8		+4	75	ANTHONY STEFANI	7	29	30	30
9				AUSTIN ROMZEK
10				BILL KNIGHT
11				BILLY UHL
12		+3	78	BRANDON LESKE	8	28	26	26
13				BRIAN KNOX
14				CHARLIE SCHULTZ
15				CHRIS KIPP
16				CRAIG COMO
17		-5	86	CRAIG MCCARVER	10	26	28	28
18				DALE VANDERVERDE
19				DAN KOSMALSKI
Player Quota History

Cell Formulas
Range	Formula
E2	E2	=Main!$AA$3
B5:B19	B5	=IFERROR(INDEX(Main!$AK$11:$AK$50,MATCH($D5,Main!$G$11:$G$50,0)),"")
C5:C19	C5	=IFERROR(INDEX(Main!$AI$11:$AI$50,MATCH(D5,Main!$G$11:$G$50,0)),"")
E5:E19	E5	=IFERROR(INDEX(Main!$I$11:$I$50,MATCH(D5,Main!$G$11:$G$50,0)),"")
F5:F19	F5	=IFERROR(INDEX(Main!$J$11:$J$50,MATCH(D5,Main!$G$11:$G$50,0)),"")
G5:G19	G5	=IFERROR(LOOKUP(2,1/($H5:$AK5<>""),$H5:$AK5),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:D141	Expression	=ISNUMBER(B6)	text	NO
D5	Expression	=ISNUMBER(B5)	text	NO
H6:H141	Expression	=AND(ISNUMBER(B6),$E$2=1)	text	NO
G5	Expression	=ISTEXT($D5)	text	NO
F5	Expression	=ISTEXT($D5)	text	NO
E5:E141	Expression	=ISTEXT($D5)	text	NO
H5	Expression	=AND(ISNUMBER(B5),$E$2=1)	text	NO
AK6:AK141	Expression	=AND(ISNUMBER($B6),$E$2=30)	text	NO
AJ6:AJ141	Expression	=AND(ISNUMBER($B6),$E$2=29)	text	NO
AI6:AI141	Expression	=AND(ISNUMBER($B6),$E$2=28)	text	NO
AH6:AH141	Expression	=AND(ISNUMBER($B6),$E$2=27)	text	NO
AG6:AG141	Expression	=AND(ISNUMBER($B6),$E$2=26)	text	NO
AF6:AF141	Expression	=AND(ISNUMBER($B6),$E$2=25)	text	NO
AE6:AE141	Expression	=AND(ISNUMBER($B6),$E$2=24)	text	NO
AD6:AD141	Expression	=AND(ISNUMBER($B6),$E$2=23)	text	NO
AC6:AC141	Expression	=AND(ISNUMBER($B6),$E$2=22)	text	NO
AB6:AB141	Expression	=AND(ISNUMBER($B6),$E$2=21)	text	NO
AA6:AA141	Expression	=AND(ISNUMBER($B6),$E$2=20)	text	NO
Z6:Z141	Expression	=AND(ISNUMBER($B6),$E$2=19)	text	NO
Y6:Y141	Expression	=AND(ISNUMBER($B6),$E$2=18)	text	NO
X6:X141	Expression	=AND(ISNUMBER($B6),$E$2=17)	text	NO
W6:W141	Expression	=AND(ISNUMBER($B6),$E$2=16)	text	NO
V6:V141	Expression	=AND(ISNUMBER($B6),$E$2=15)	text	NO
U6:U141	Expression	=AND(ISNUMBER($B6),$E$2=14)	text	NO
T6:T141	Expression	=AND(ISNUMBER($B6),$E$2=13)	text	NO
S6:S141	Expression	=AND(ISNUMBER($B6),$E$2=12)	text	NO
R6:R141	Expression	=AND(ISNUMBER($B6),$E$2=11)	text	NO
Q6:Q141	Expression	=AND(ISNUMBER($B6),$E$2=10)	text	NO
P6:P141	Expression	=AND(ISNUMBER($B6),$E$2=9)	text	NO
O6:O141	Expression	=AND(ISNUMBER($B6),$E$2=8)	text	NO
N6:N141	Expression	=AND(ISNUMBER($B6),$E$2=7)	text	NO
M6:M141	Expression	=AND(ISNUMBER($B6),$E$2=6)	text	NO
L6:L141	Expression	=AND(ISNUMBER($B6),$E$2=5)	text	NO
K6:K141	Expression	=AND(ISNUMBER($B6),$E$2=4)	text	NO
J6:J141	Expression	=AND(ISNUMBER($B6),$E$2=3)	text	NO
I6:I141	Expression	=AND(ISNUMBER($B6),$E$2=2)	text	NO
B6:C141	Expression	=ISNUMBER($B6)	text	NO
F7:F26	Expression	=ISTEXT($D7)	text	NO
G6	Expression	=ISTEXT($D6)	text	NO
F6	Expression	=ISTEXT($D6)	text	NO
G6:G141,F27:F141	Expression	=ISTEXT($D6)	text	NO
D4	Expression	=ISNUMBER(#REF!)	text	NO
H4	Expression	=AND(ISNUMBER(#REF!),$E$2=1)	text	NO
I4	Expression	=AND(ISNUMBER(#REF!),$E$2=2)	text	NO
J4	Expression	=AND(ISNUMBER(#REF!),$E$2=3)	text	NO
K4	Expression	=AND(ISNUMBER(#REF!),$E$2=4)	text	NO
L4	Expression	=AND(ISNUMBER(#REF!),$E$2=5)	text	NO
M4	Expression	=AND(ISNUMBER(#REF!),$E$2=6)	text	NO
N4	Expression	=AND(ISNUMBER(#REF!),$E$2=7)	text	NO
O4	Expression	=AND(ISNUMBER(#REF!),$E$2=8)	text	NO
P4	Expression	=AND(ISNUMBER(#REF!),$E$2=9)	text	NO
Q4	Expression	=AND(ISNUMBER(#REF!),$E$2=10)	text	NO
R4	Expression	=AND(ISNUMBER(#REF!),$E$2=11)	text	NO
S4	Expression	=AND(ISNUMBER(#REF!),$E$2=12)	text	NO
T4	Expression	=AND(ISNUMBER(#REF!),$E$2=13)	text	NO
U4	Expression	=AND(ISNUMBER(#REF!),$E$2=14)	text	NO
V4	Expression	=AND(ISNUMBER(#REF!),$E$2=15)	text	NO
W4	Expression	=AND(ISNUMBER(#REF!),$E$2=16)	text	NO
X4	Expression	=AND(ISNUMBER(#REF!),$E$2=17)	text	NO
Y4	Expression	=AND(ISNUMBER(#REF!),$E$2=18)	text	NO
Z4	Expression	=AND(ISNUMBER(#REF!),$E$2=19)	text	NO
AA4	Expression	=AND(ISNUMBER(#REF!),$E$2=20)	text	NO
AB4	Expression	=AND(ISNUMBER(#REF!),$E$2=21)	text	NO
AC4	Expression	=AND(ISNUMBER(#REF!),$E$2=22)	text	NO
AD4	Expression	=AND(ISNUMBER(#REF!),$E$2=23)	text	NO
AE4	Expression	=AND(ISNUMBER(#REF!),$E$2=24)	text	NO
AF4	Expression	=AND(ISNUMBER(#REF!),$E$2=25)	text	NO
AG4	Expression	=AND(ISNUMBER(#REF!),$E$2=26)	text	NO
AH4	Expression	=AND(ISNUMBER(#REF!),$E$2=27)	text	NO
AI4	Expression	=AND(ISNUMBER(#REF!),$E$2=28)	text	NO
AJ4	Expression	=AND(ISNUMBER(#REF!),$E$2=29)	text	NO
AK4	Expression	=AND(ISNUMBER(#REF!),$E$2=30)	text	NO

 

[felixstraube]

So for each round you will enter new values in column E (HDCP) and you want the next round column I, J, etc will be updated with that new value (considering the adjustment rules)?

 

[VinceF]

Felix,
Thank you for your interest/assistance
No, the number in column E (HDCP) is only used to establish the players hdcp for the 1st round represented in column F, after that those numbers never come into play again.
Column G represents the players adjusted quota of which is reported back to the main page and is the quota number that the player will use for the next round.
Round 1 was already manually adjusted by putting a number in column H which adjusted the (adjusted quota) in column G. Going forward the adjusted number would be placed in the round# that was played. This example shows that were playing round 2 and I would take the number in column B, adjust according to the table in row F and that number would be placed in column i
In other words Adam was -8 under his quota, so I would take the 35 in G6 and reduce his quota by 2 and which makes it a 33 and the 33 would be placed in I6

Hope this helps...

 

[VinceF]

Do I need to try and explain it in more detail?

VinceF