Change calculation method with first re-occurrence of a column value

[z3r01]

Hi community.

I need some directions to solve a specific issue I have. The calculations below are made by referencing multiple other Worksheets. Those hold dynamic tables or defined ranges of such tables. The tables are retrieved from within a SQL database. The layout of the Workbook I'm working on is the following;

PKID	Change	RateH	TCH	TCNVH	TF1	HST	AST	TF2	TCNVA	TCA	RateA	TCD	TCF	GCD	G	R	Rdf	K	W	Year	NV
93	49.76	1,315.58	3.0	""	TextA	190	187	TextB	""	3.5	1,431.81	-40	40	3	1.750	1	-156	40	28.918694%	2012	0
99	-19.29	2,020.80	5.0	""	TextC	300	300	TextD	""	3.5	1,443.78	120	40	1	1.000	0.5	697	40	98.223097%	2012	0
121	-42.55	1,480.15	3.5	""	TextE	100	102	TextA	""	3.0	1,365.34	40	40	2	1.500	0	155	40	70.912927%	2012	0
127	7.04	1,463.07	3.5	""	TextD	201	201	TextF	""	4.0	1,550.76	-40	40	1	1.000	0.5	-128	40	32.409231%	2012	0

<tbody>
</tbody>

Definitions and Functions:

Column A = Integer (Unique respectively Primary Key) function

=IFERROR(INDEX(PKID,ROWS('HistDB'!A$2:A2)),"")

Column B = Decimal[2] - function

=IF(F3="","",IF(AND(M3<0,N3<=SUM($S3+2),G3<H3,O3=1),SUM(P3*N3*(Q3-T3)),IF(AND(Q3<1,N3>SUM($S3+2),O3<=2),SUM(P3*N3*(Q3-T3)),SUM(S3*P3*(Q3-T3)))))

Column C = Decimal[2] - function

=IF(F3="","",HLOOKUP(F3,Rates,ROWS(F$1:F3),FALSE))

Column D = Decimal[1] - function

[COLOR=#FF8C00]=IF(F3="","",IF(E3="",HLOOKUP(F3,TC,2,FALSE),""))[/COLOR]

Column E = Decimal[1] - function

=IFERROR(IF(V3=0,HLOOKUP(F3,[COLOR=#FF8C00]TC[/COLOR],2,FALSE),1),"")

Column F = Varchar[25] - function

=IFERROR(INDEX([COLOR=#B22222]TF[/COLOR],ROWS('HistDB'!B$2:B2)),"")

Column G = Integer (variable) - function

=IFERROR(INDEX(HST,ROWS('HistDB'!D$2:D2)),"")

Column H = Integer (variable) - function

=IFERROR(INDEX(AST,ROWS('HistDB'!E$2:E2)),"")

Column I = Varchar[25] - function

=IFERROR(INDEX([COLOR=#B22222]AF[/COLOR],ROWS('HistDB'!E$2:E2)),"")

Column J = Decimal[1] - function

=IFERROR(IF(V3=0,HLOOKUP(I3,TC,2,FALSE),1),"")

Column K = Decimal[1] - function

[COLOR=#FF8C00]=IF(I3="","",IF(J3="",HLOOKUP(I3,TC,2,FALSE),""))[/COLOR]

Column L = Decimal[2] - function

=IF(I3="","",HLOOKUP(I3,Rates,ROWS(I$1:I3),FALSE))

Column M = Integer - function

=IF(F3="","",IF(E3="",IF(D3>K3,SUM(D3-K3)*80,SUM(D3-K3)*80),IF(E3>J3,SUM(E3-J3)*80,SUM(E3-J3)*80)))

Column N = Integer - function

=IFERROR(IF($M3=-160,SUM($S3+2),IF($M3=-200,SUM($S3+3),IF($M3=-240,SUM($S3+4),IF($M3=-280,SUM($S3+5),IF($M3=-320,SUM($S3+6), IF($M3=160,SUM($S3+7),IF($M3=200,SUM($S3+8),IF($M3=240,SUM($S3+9),IF($M3=280,SUM($S3+10),IF($M3=320,SUM($S3+11),SUM($S3))))))))))),"")

Column O = Integer - function

=IF(F3="","",IF(G3=H3,1,IF(G3>H3,SUM(G3-H3),SUM(H3-G3))))

Column P = Decimal[3] - function

=IF(F3="","",IF(O3=1,1,(IF(O3=2,1.5,SUM((11+O3)/8)))))

Column Q = Decimal[1] - function

=IF(F3="","",IF(G3=H3,0.5,IF(G3>H3,1,0)))

Column R = Integer - function

=IF(F3="","",C3-L3+M3)

Column S = Integer - function

=IFERROR(INDEX(KFactor,ROWS('HistDB'!G$2:G2)),"")

Column T = Decimal[6] - function

=IF(F3="","",SUM(1/(10^(-R3/400)+1)))

Column U = Date[4] - function

=IFERROR(INDEX(YearF,ROWS('HistDB'!L$2:L2)),"")

Column V = Boolean - function =IFERROR(INDEX(NVF,ROWS('HistDB'!H$2:H2)),1)


Now with the layout and definitions available, the description of what I need to achieve. Basically, whenever Column F[TF1] OR Column I[TF2] get their first re-occurrence within the dynamic range, the defined function to retrieve the value(s) within Column D[TCH] respectively Column K[TCA] should be looking up the changed value based on the above calculations. Therefore, the function;

=IF(F3="","",IF(E3="",HLOOKUP(F3,TC,2,FALSE),""))

needs another IF statement respectively a new table or defined range to retrieve the new value from.

Issue 1)

To get the 2nd occurrence of Column F[TF1] I could use the following function;

=INDEX(CGCalc[PKID],SMALL(IF(Range=CGCalc[@[TF]],ROW(Range)-2,""),2),1)

However, if the re-occurrence happens within TF2 the above function changes to;

=INDEX(CGCalc[PKID],SMALL(IF(Range=CGCalc[@[AF]],ROW(Range)-2,""),2),1)

a) is there a (easier/better) way to retrieve the position of the re-occurrence and b) possibly in one step (regardless if the occurrence happens either in TF1 or TF2)?

Issue 2)

Now the part where it gets really dirty. After calculating the first, and only the first re-occurrence (there can be many more afterwards), the calculations within Column D[TCH] respectively Column K[TCA] must be changed to reflect the fact that from now on the value(s) to be retrieved into Columns D respectively Column K aren't anymore in the defined range [TF and/or AF] used within the Column D and K functions. The two ranges TF and AF are holding a defined range with unique text values TF1 and TF2 and the initial integers TC used within the functions in Columns D and K.

So, the "orange" marked code will and must change. Of course, based on that change many other values and calculations are affected. The colors navy blue respectively green indicate the Columns/Functions to be changed. The red color indicates the two defined ranges which are now used to retrieve the initial values to allow the calculations within Column D respectively Column K.

I hope I explained everything understandable enough, if not just shoot with questions. I will be grateful for any help anyone can provide.