Running total that resets

L

Lobsterboy1

Board Regular
Joined
Aug 5, 2016
Messages
90
Hi, I am trying to find a way to automatically do some formulas that I currently do manually. I think the only way will be through VBA which I kind of understand but not enough to do this. This is what I have.

A B C D BOX
174a 100 mirror
174b 62 mirror
175a 51 51 A B
175b 33 84 B C
175c 1 85 C
178a 25 25 A
178b 21 46 A B
178c 52 98 B C D
178d 13 111 D
178e 11 122 D E
178f 17 139 E
178g 54 193 E F
178h 50 243 F G
178i 3 246 G
173 26 26 A
179a 60 60 A B
179b 57 117 C D
179c 60 177 D E F
179d 60 237 F G H
179e 26 263 H I
179f 4 267 I
179g 60 327 I J K
179h 59 386 K L M

Column A is a run number
Column B is the amount
Column C might have "Mirror" in if it does no formula is required
Column D is a running total of the run number, this resets when the run number changes. This column is not actually needed it just helps me to work the box letters out.
Column E is the box letters that the items go into, there are always 30 items per box

Columns A B and C are already on the worksheet, I make columns D and E with E being my final goal.

what I am after automatically doing is working out the box letters for the production runs, so in 175a there are 51 items so 30 will go in box A. Leaving 21 to go in box B. This means out of 175b 9 will go into box B to make it up to 30 and the remaining 24 will go into box C and so on through out the run, the last box might not be full which is fine. This starts again at 178a and carry's on down the list. If column C contains the text "MIRROR" i dont want anything in column E.

Is there a formula I could use to populate the box letters or is VBA the only way.

Thanks.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
i think i can help you here, but i need more clarification and what exactly you need
 
Last edited by a moderator:
Upvote 0
I have some code that I think will work, but my output is a little different than yours.

For one, are the values in column E a single concatenated string of the boxes or does each box (A, B, C, etc) occupy it's own cell (col E, col F, col G)?

Second, how many boxes are allowed in each line? Some have three, some have two.

Lastly, I get different results for the 178 series; by my code, I get up to box I.

If you can give me some feedback, I can tweak it to your specifications.
 
Upvote 0
I figured out the answer to question 2; and assumed that it was a string output. Still don't know what's going on in series 178.

In case this is handling a large dataset, I used arrays instead of looping thru the range. It's much faster.

Change the Worksheet to the name of what yours is; the delimiter (separator) for the text string out can be changed to anything you'd like. There's issues with UsedRange not capturing all data, but it's convenient for illustrative purposes.

Let me know if this works for you.

Code: 
Sub test_FillBoxesII()

Dim sht As Worksheet
  Set sht = Worksheets("Sheet2")

Dim arr As Variant
  arr = sht.UsedRange.value
 ' ReDim Preserve arr(LBound(arr, 1) To UBound(arr, 1), _
                     LBound(arr, 2) To UBound(arr, 2))  ' ~~ This is needed if the output is in col D (I just used column C since there was space already

Dim delim As String
  delim = "|"
  
Dim i As Long, _
    ID As Long, _
    cntr As Long, _
    cntrBox As Long, _
    colID As Long, _
    colCnt As Long
  colID = 1
  colCnt = 2
  
Dim remaindr As Long, _
    quotient As Long, _
    div As Long
  div = 30

  For i = LBound(arr, 1) To UBound(arr, 1)

    ' ~~ Test if ID changes
    If Left(arr(i, colID), 3) <> ID Then
      ID = Left(arr(i, colID), 3)
      remaindr = 0
      cntrBox = 1
    End If
    
    On Error Resume Next
    If Left(arr(i, colID), 3) = ID Or _
       Left(arr(i, colID), 3) = Left(arr(i - 1, colID), 3) Then
    On Error GoTo 0
      
      If arr(i, 3) <> "mirror" Then  ' ~~ If third column contains "mirror", skip; otherwise,
        
        quotient = Int((arr(i, colCnt) + remaindr) / div)
        remaindr = (arr(i, colCnt) + remaindr) Mod div
        
        If quotient > 0 Then
          For cntr = 1 To quotient
            If cntr = 1 Then
              arr(i, 3) = Chr(64 + cntrBox)  ' ~~ Output is to column C
            Else
              arr(i, 3) = arr(i, 3) & delim & Chr(64 + cntrBox)
            End If
            cntrBox = cntrBox + 1
          Next cntr
        End If
        
        If remaindr > 0 Then
          If quotient = 0 Then
            arr(i, 3) = Chr(64 + cntrBox)
          Else
            arr(i, 3) = arr(i, 3) & delim & Chr(64 + cntrBox)
          End If
        End If
        
      End If '<> "mirror"
    End If 'Left
  Next i
  
  With ActiveWorkbook.Worksheets.Add
    .Range(.Cells(1, 1), .Cells(UBound(arr, 1), UBound(arr, 2))) = arr
  End With
  
End Sub
 
Upvote 0
Interesting thread. Try this.


Just one detail, in this number 173, you could put 173a
Code: 
Option Explicit
Dim cant As Long, i As Long, j As Long, resi As Long, debo As Boolean
Sub test14()
    Dim n As String, b As Range
    Range("H:J").ClearContents
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If LCase(Cells(i, "C")) <> LCase("mirror") Then
            n = "" & Left(Cells(i, "A"), 3)
            Set b = Range("A:A").Find(n & "?", lookat:=xlPart, searchdirection:=xlPrevious)
            j = 8
            cant = Cells(i, "B")
            resi = 0
            debo = False
            Do While i < b.Row + 1
                If cant > 30 Then
                    Call first
                Else
                    If debo Then
                        If cant >= 30 - resi Then
                            Call first
                        Else
                            Call second(1)
                        End If
                    Else
                        Call second(2)
                    End If
                End If
            Loop
            i = b.Row
        End If
    Next
End Sub


Sub first()
    Cells(i, j) = 30 - resi
    cant = cant - (30 - resi)
    j = j + 1
    resi = 0
    debo = False
End Sub
    
Sub second(op)
    Cells(i, j) = cant
    If op = 1 Then
        resi = resi + cant
    Else
        resi = IIf(cant = 30, 0, cant)
    End If
    i = i + 1
    cant = Cells(i, "B")
    j = 8
    debo = True
End Sub

I put the result starting in column H:

Excel Workbook
ABCDEFGHIJ
1
2174a100mirror
3174b62mirror
4175a5151AB3021
5175b3384BC924
6175c185C1
7178a2525A25
8178b2146AB516
9178c5298BCD14308
10178d13111D13
11178e11122DE92
12178f17139E17
13178g54193EF113013
14178h50243FG17303
15178i3246G3
16173a2626A26
17179a6060AB3030
18179b57117CD3027
19179c60177DEF33027
20179d60237FGH33027
21179e26263HI323
22179f4267I4
23179g60327IJK33027
24179h59386KLM33026
Sheet
 
Upvote 0
Try this:-
Data assumed to start on row 2

My understanding is you wanted the "Box" letter placing (Starting Column D) against the relevant Amounts (Column B).
In this code I have done that and also place the amounts Relevant to a particular box at that position.
My Calculations seems to be at odds with rows 13,14 & 15 of your data.
The logic to me is that after running the code the total of each row of numbers (starting column D) should add to the amount in same row in column "B".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG19May03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, tot [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRay() [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nTot [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dn.Offset(, 2).Value = "mirror" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .Exists(Left(Dn.Value, 3)) [COLOR="Navy"]Then[/COLOR]
            .Add Left(Dn.Value, 3), Dn.Offset(, 1)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] .Item(Left(Dn.Value, 3)) = Union(.Item(Left(Dn.Value, 3)), Dn.Offset(, 1))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]


[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] M [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, G [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
  tot = 0: Num = 65
  ReDim Ray(1 To Application.Sum(.Item(K)), 1 To 2)

  [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(K)
    [COLOR="Navy"]For[/COLOR] n = 1 To R.Value
        c = c + 1
        p = p + 1
        p = IIf(p Mod 31 = 0, 1, p)
        Ray(c, 1) = n: Ray(c, 2) = p
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] R


M = 1
c = 0
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Ray(n, 1) = .Item(K)(M) [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        M = M + 1
        G = G + 1
        ReDim Preserve nRay(1 To G)
        nRay(G) = Application.Min(Ray(n, 1), Ray(n, 2))
    [COLOR="Navy"]ElseIf[/COLOR] Ray(n, 2) = 30 [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            G = G + 1
            ReDim Preserve nRay(1 To G)
            nRay(G) = Application.Min(Ray(n, 1), Ray(n, 2))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n

Num = 65: c = 0
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] .Item(K)
    Ac = 2: tot = 0
        [COLOR="Navy"]Do[/COLOR] Until Dn.Value = tot
            c = c + 1
            tot = tot + nRay(c)
            Dn.Offset(, Ac) = Chr(Num) & " " & nRay(c)
            nTot = nTot + nRay(c)
            Ac = Ac + 1
            [COLOR="Navy"]If[/COLOR] nTot Mod 30 = 0 [COLOR="Navy"]Then[/COLOR] Num = Num + 1
        [COLOR="Navy"]Loop[/COLOR]
[COLOR="Navy"]Next[/COLOR] Dn

Erase nRay: c = 0: tot = 0: nTot = 0: p = 0: G = 0
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
Regards Mick
 
Upvote 0
Hi, Dante
It nice to see we both have the same (Number) answers.. What about the letters ??
I would have thought they were both required , but the OP only seems to be only asking for the Letters. !!!
 
Upvote 0
Hi Mike:

I understood that the OP wanted the numbers :laugh:
 
Upvote 0
Hi, Thanks for all your reply's, MickG I like the output from your code. As usually happens my boss has moved the goalposts and wants me to put it onto the main sheet. I have looked through but I am nowhere near good enough to adapt your code to what he wants. Basically its the same thing on a different sheet with a lot more data and formulas. I have had to remove so text from this but it should not matter.

Book1
ABCDEFGHIJKLMNOPQR
1PRODUCTION LIST20-MayTOTAL
2
3DELBATCHQTYBATCH RUNQTYUNITS LEFT
422-May181b36NO36 
522-May211a212mirrored batchNO212 
622-May211b69mirrored batchNO69 
722-May211c168mirrored batch - GEONO168 
822-May212a42NO42 
922-May212b11NO11 
1022-May212c30NO30 
1122-May212d5NO5 
1222-May212e5NO5 
1322-May212f14NO14 
1422-May212g50NO50 
1522-May212h3NO3 
1622-May212i51NO51 
1722-May212j1NO1 
1822-May212k3NO3 
1922-May205c11NO11 
2022-May205d29NO29 
2122-May219a14NO14 
2222-May219b3NO3 
2322-May213a82mirrored batchNO82 
2422-May213b94mirrored batchNO94 
2522-May213c8mirrored batchNO8 
2622-May213d1mirrored batchNO1 
2722-May213e42mirrored batch - GEONO42 
2822-May218a5GEONO5 
2922-May218b55NO55 
3022-May218c28NO28 
3122-May215a102mirrored batchNO102 
3222-May215b10mirrored batch - GEONO10 
3322-May216a57NO57 
3422-May216b18NO18 
3522-May220a26NO26 
3622-May220b23NO23 
3722-May220c60NO60 
3822-May220d23NO23 
3922-May220e11NO11 
4022-May220f3NO3 
4122-May220g17NO17 
4222-May220h45NO45 
4322-May220i6NO6 
4422-May21745NO45 
4522-May221a46NO46 
4622-May221b60NO60 
4722-May221c10NO10 
4822-May221d19NO19 
4922-May221e59NO59 
5022-May221f50NO50 
5122-May222a8NO8 
5222-May222b3NO3 
5322-May222c60NO60 
5422-May222d18NO18 
5522-May222e27NO27 
5622-May222f60NO60 
5722-May222g9NO9 
5822-May214a90NO90 
5922-May214b87NO87 
6022-May214c90NO90 
6122-May214d90NO90 
6222-May214e33NO33 
6322-May214f26NO26 
6422-May214g15NO15 
6522-May214h10NO10 
6622-May214i12NO12 
6722-May214j20NO20 
6822-May214k6NO6 
6922-MayOBH255NO55 
7022-May237a30NO30 
7122-May237b1NO1 
7222-May237c13NO13 
7325252525
7423-May219c28NO28 
7523-May219d30NO30 
7623-May133142NO142 
77200200
7828-May16712NO12 
791212
8029-MayOBH41NO1 
8111
TOTAL
Cell Formulas
RangeFormula
F1=TODAY()
Q4=IF(P4="NO",E4,"")
Q5=IF(P5="NO",E5,"")
Q6=IF(P6="NO",E6,"")
Q7=IF(P7="NO",E7,"")
Q8=IF(P8="NO",E8,"")
Q9=IF(P9="NO",E9,"")
Q10=IF(P10="NO",E10,"")
Q11=IF(P11="NO",E11,"")
Q12=IF(P12="NO",E12,"")
Q13=IF(P13="NO",E13,"")
Q14=IF(P14="NO",E14,"")
Q15=IF(P15="NO",E15,"")
Q16=IF(P16="NO",E16,"")
Q17=IF(P17="NO",E17,"")
Q18=IF(P18="NO",E18,"")
Q19=IF(P19="NO",E19,"")
Q20=IF(P20="NO",E20,"")
Q21=IF(P21="NO",E21,"")
Q22=IF(P22="NO",E22,"")
Q23=IF(P23="NO",E23,"")
Q24=IF(P24="NO",E24,"")
Q25=IF(P25="NO",E25,"")
Q26=IF(P26="NO",E26,"")
Q27=IF(P27="NO",E27,"")
Q28=IF(P28="NO",E28,"")
Q29=IF(P29="NO",E29,"")
Q30=IF(P30="NO",E30,"")
Q31=IF(P31="NO",E31,"")
Q32=IF(P32="NO",E32,"")
Q33=IF(P33="NO",E33,"")
Q34=IF(P34="NO",E34,"")
Q35=IF(P35="NO",E35,"")
Q36=IF(P36="NO",E36,"")
Q37=IF(P37="NO",E37,"")
Q38=IF(P38="NO",E38,"")
Q39=IF(P39="NO",E39,"")
Q40=IF(P40="NO",E40,"")
Q41=IF(P41="NO",E41,"")
Q42=IF(P42="NO",E42,"")
Q43=IF(P43="NO",E43,"")
Q44=IF(P44="NO",E44,"")
Q45=IF(P45="NO",E45,"")
Q46=IF(P46="NO",E46,"")
Q47=IF(P47="NO",E47,"")
Q48=IF(P48="NO",E48,"")
Q49=IF(P49="NO",E49,"")
Q50=IF(P50="NO",E50,"")
Q51=IF(P51="NO",E51,"")
Q52=IF(P52="NO",E52,"")
Q53=IF(P53="NO",E53,"")
Q54=IF(P54="NO",E54,"")
Q55=IF(P55="NO",E55,"")
Q56=IF(P56="NO",E56,"")
Q57=IF(P57="NO",E57,"")
Q58=IF(P58="NO",E58,"")
Q59=IF(P59="NO",E59,"")
Q60=IF(P60="NO",E60,"")
Q61=IF(P61="NO",E61,"")
Q62=IF(P62="NO",E62,"")
Q63=IF(P63="NO",E63,"")
Q64=IF(P64="NO",E64,"")
Q65=IF(P65="NO",E65,"")
Q66=IF(P66="NO",E66,"")
Q67=IF(P67="NO",E67,"")
Q68=IF(P68="NO",E68,"")
Q69=IF(P69="NO",E69,"")
Q70=IF(P70="NO",E70,"")
Q71=IF(P71="NO",E71,"")
Q72=IF(P72="NO",E72,"")
Q73=SUM($Q$4:$R$72)
Q74=IF(P74="NO",E74,"")
Q75=IF(P75="NO",E75,"")
Q76=IF(P76="NO",E76,"")
Q77=SUM($Q$74:$R$76)
Q78=IF(P78="NO",E78,"")
Q79=SUM($Q$78:$R$78)
Q80=IF(P80="NO",E80,"")
Q81=SUM($Q$80:$R$80)
P4{=IFERROR(VLOOKUP(B4,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B4)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P5{=IFERROR(VLOOKUP(B5,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B5)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P6{=IFERROR(VLOOKUP(B6,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B6)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P7{=IFERROR(VLOOKUP(B7,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B7)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P8{=IFERROR(VLOOKUP(B8,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B8)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P9{=IFERROR(VLOOKUP(B9,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B9)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P10{=IFERROR(VLOOKUP(B10,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B10)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P11{=IFERROR(VLOOKUP(B11,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B11)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P12{=IFERROR(VLOOKUP(B12,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B12)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P13{=IFERROR(VLOOKUP(B13,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B13)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P14{=IFERROR(VLOOKUP(B14,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B14)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P15{=IFERROR(VLOOKUP(B15,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B15)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P16{=IFERROR(VLOOKUP(B16,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B16)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P17{=IFERROR(VLOOKUP(B17,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B17)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P18{=IFERROR(VLOOKUP(B18,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B18)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P19{=IFERROR(VLOOKUP(B19,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B19)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P20{=IFERROR(VLOOKUP(B20,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B20)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P21{=IFERROR(VLOOKUP(B21,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B21)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P22{=IFERROR(VLOOKUP(B22,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B22)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P23{=IFERROR(VLOOKUP(B23,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B23)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P24{=IFERROR(VLOOKUP(B24,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B24)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P25{=IFERROR(VLOOKUP(B25,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B25)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P26{=IFERROR(VLOOKUP(B26,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B26)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P27{=IFERROR(VLOOKUP(B27,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B27)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P28{=IFERROR(VLOOKUP(B28,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B28)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P29{=IFERROR(VLOOKUP(B29,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B29)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P30{=IFERROR(VLOOKUP(B30,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B30)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P31{=IFERROR(VLOOKUP(B31,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B31)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P32{=IFERROR(VLOOKUP(B32,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B32)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P33{=IFERROR(VLOOKUP(B33,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B33)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P34{=IFERROR(VLOOKUP(B34,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B34)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P35{=IFERROR(VLOOKUP(B35,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B35)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P36{=IFERROR(VLOOKUP(B36,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B36)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P37{=IFERROR(VLOOKUP(B37,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B37)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P38{=IFERROR(VLOOKUP(B38,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B38)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P39{=IFERROR(VLOOKUP(B39,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B39)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P40{=IFERROR(VLOOKUP(B40,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B40)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P41{=IFERROR(VLOOKUP(B41,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B41)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P42{=IFERROR(VLOOKUP(B42,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B42)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P43{=IFERROR(VLOOKUP(B43,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B43)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P44{=IFERROR(VLOOKUP(B44,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B44)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P45{=IFERROR(VLOOKUP(B45,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B45)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P46{=IFERROR(VLOOKUP(B46,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B46)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P47{=IFERROR(VLOOKUP(B47,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B47)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P48{=IFERROR(VLOOKUP(B48,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B48)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P49{=IFERROR(VLOOKUP(B49,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B49)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P50{=IFERROR(VLOOKUP(B50,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B50)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P51{=IFERROR(VLOOKUP(B51,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B51)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P52{=IFERROR(VLOOKUP(B52,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B52)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P53{=IFERROR(VLOOKUP(B53,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B53)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P54{=IFERROR(VLOOKUP(B54,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B54)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P55{=IFERROR(VLOOKUP(B55,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B55)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P56{=IFERROR(VLOOKUP(B56,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B56)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P57{=IFERROR(VLOOKUP(B57,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B57)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P58{=IFERROR(VLOOKUP(B58,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B58)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P59{=IFERROR(VLOOKUP(B59,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B59)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P60{=IFERROR(VLOOKUP(B60,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B60)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P61{=IFERROR(VLOOKUP(B61,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B61)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P62{=IFERROR(VLOOKUP(B62,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B62)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P63{=IFERROR(VLOOKUP(B63,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B63)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P64{=IFERROR(VLOOKUP(B64,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B64)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P65{=IFERROR(VLOOKUP(B65,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B65)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P66{=IFERROR(VLOOKUP(B66,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B66)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P67{=IFERROR(VLOOKUP(B67,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B67)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P68{=IFERROR(VLOOKUP(B68,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B68)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P69{=IFERROR(VLOOKUP(B69,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B69)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P70{=IFERROR(VLOOKUP(B70,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B70)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P71{=IFERROR(VLOOKUP(B71,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B71)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P72{=IFERROR(VLOOKUP(B72,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B72)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P74{=IFERROR(VLOOKUP(B74,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B74)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P75{=IFERROR(VLOOKUP(B75,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B75)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P76{=IFERROR(VLOOKUP(B76,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B76)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P78{=IFERROR(VLOOKUP(B78,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B78)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
P80{=IFERROR(VLOOKUP(B80,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$P$500"),B80)>0),0))&"'!$B$4:$P$500"),15,FALSE),"")}
R4{=IF(VLOOKUP(B4,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B4)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B4,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B4)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R5{=IF(VLOOKUP(B5,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B5)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B5,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B5)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R6{=IF(VLOOKUP(B6,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B6)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B6,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B6)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R7{=IF(VLOOKUP(B7,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B7)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B7,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B7)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R8{=IF(VLOOKUP(B8,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B8)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B8,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B8)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R9{=IF(VLOOKUP(B9,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B9)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B9,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B9)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R10{=IF(VLOOKUP(B10,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B10)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B10,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B10)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R11{=IF(VLOOKUP(B11,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B11)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B11,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B11)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R12{=IF(VLOOKUP(B12,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B12)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B12,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B12)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R13{=IF(VLOOKUP(B13,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B13)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B13,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B13)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R14{=IF(VLOOKUP(B14,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B14)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B14,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B14)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R15{=IF(VLOOKUP(B15,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B15)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B15,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B15)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R16{=IF(VLOOKUP(B16,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B16)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B16,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B16)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R17{=IF(VLOOKUP(B17,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B17)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B17,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B17)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R18{=IF(VLOOKUP(B18,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B18)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B18,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B18)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R19{=IF(VLOOKUP(B19,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B19)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B19,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B19)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R20{=IF(VLOOKUP(B20,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B20)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B20,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B20)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R21{=IF(VLOOKUP(B21,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B21)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B21,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B21)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R22{=IF(VLOOKUP(B22,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B22)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B22,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B22)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R23{=IF(VLOOKUP(B23,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B23)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B23,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B23)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R24{=IF(VLOOKUP(B24,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B24)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B24,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B24)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R25{=IF(VLOOKUP(B25,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B25)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B25,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B25)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R26{=IF(VLOOKUP(B26,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B26)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B26,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B26)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R27{=IF(VLOOKUP(B27,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B27)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B27,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B27)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R28{=IF(VLOOKUP(B28,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B28)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B28,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B28)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R29{=IF(VLOOKUP(B29,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B29)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B29,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B29)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R30{=IF(VLOOKUP(B30,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B30)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B30,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B30)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R31{=IF(VLOOKUP(B31,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B31)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B31,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B31)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R32{=IF(VLOOKUP(B32,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B32)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B32,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B32)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R33{=IF(VLOOKUP(B33,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B33)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B33,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B33)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R34{=IF(VLOOKUP(B34,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B34)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B34,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B34)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R35{=IF(VLOOKUP(B35,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B35)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B35,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B35)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R36{=IF(VLOOKUP(B36,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B36)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B36,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B36)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R37{=IF(VLOOKUP(B37,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B37)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B37,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B37)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R38{=IF(VLOOKUP(B38,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B38)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B38,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B38)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R39{=IF(VLOOKUP(B39,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B39)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B39,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B39)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R40{=IF(VLOOKUP(B40,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B40)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B40,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B40)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R41{=IF(VLOOKUP(B41,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B41)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B41,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B41)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R42{=IF(VLOOKUP(B42,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B42)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B42,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B42)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R43{=IF(VLOOKUP(B43,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B43)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B43,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B43)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R44{=IF(VLOOKUP(B44,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B44)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B44,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B44)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R45{=IF(VLOOKUP(B45,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B45)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B45,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B45)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R46{=IF(VLOOKUP(B46,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B46)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B46,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B46)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R47{=IF(VLOOKUP(B47,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B47)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B47,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B47)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R48{=IF(VLOOKUP(B48,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B48)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B48,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B48)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R49{=IF(VLOOKUP(B49,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B49)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B49,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B49)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R50{=IF(VLOOKUP(B50,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B50)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B50,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B50)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R51{=IF(VLOOKUP(B51,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B51)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B51,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B51)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R52{=IF(VLOOKUP(B52,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B52)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B52,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B52)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R53{=IF(VLOOKUP(B53,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B53)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B53,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B53)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R54{=IF(VLOOKUP(B54,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B54)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B54,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B54)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R55{=IF(VLOOKUP(B55,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B55)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B55,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B55)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R56{=IF(VLOOKUP(B56,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B56)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B56,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B56)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R57{=IF(VLOOKUP(B57,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B57)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B57,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B57)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R58{=IF(VLOOKUP(B58,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B58)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B58,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B58)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R59{=IF(VLOOKUP(B59,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B59)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B59,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B59)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R60{=IF(VLOOKUP(B60,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B60)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B60,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B60)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R61{=IF(VLOOKUP(B61,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B61)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B61,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B61)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R62{=IF(VLOOKUP(B62,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B62)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B62,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B62)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R63{=IF(VLOOKUP(B63,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B63)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B63,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B63)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R64{=IF(VLOOKUP(B64,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B64)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B64,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B64)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R65{=IF(VLOOKUP(B65,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B65)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B65,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B65)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R66{=IF(VLOOKUP(B66,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B66)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B66,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B66)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R67{=IF(VLOOKUP(B67,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B67)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B67,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B67)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R68{=IF(VLOOKUP(B68,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B68)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B68,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B68)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R69{=IF(VLOOKUP(B69,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B69)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B69,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B69)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R70{=IF(VLOOKUP(B70,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B70)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B70,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B70)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R71{=IF(VLOOKUP(B71,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B71)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B71,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B71)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R72{=IF(VLOOKUP(B72,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B72)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B72,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B72)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R74{=IF(VLOOKUP(B74,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B74)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B74,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B74)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R75{=IF(VLOOKUP(B75,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B75)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B75,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B75)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R76{=IF(VLOOKUP(B76,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B76)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B76,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B76)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R78{=IF(VLOOKUP(B78,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B78)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B78,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B78)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
R80{=IF(VLOOKUP(B80,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B80)>0),0))&"'!$B$4:$R$500"),17,FALSE)=0,"",VLOOKUP(B80,INDIRECT("'"&INDEX(SHEETLIST,MATCH(1,--(COUNTIF(INDIRECT("'"&SHEETLIST&"'!$B$4:$R$500"),B80)>0),0))&"'!$B$4:$R$500"),17,FALSE))}
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
SHEETLIST=DATA!$C$1:$C$4


The Box letters would need to start in column S. And with the rows 13, 14 and 15 in my previous post It was my workings out which were wrong. Thanks for all your help.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Need data where ever we get all the letters
Replies
3
Views
154
Peter_SSs
Peter_SSs
K
  • Locked
  • Question Question
Vba code for User history log which is giving me a run-time error "13" mismatch
Replies
2
Views
168
Fluff
Fluff
M
  • Solved
Macro stops after message box appear
Replies
3
Views
597
bobsan42
bobsan42
ExcelNewbie2020
  • Solved
sum function that will reset the running total every after 5 counts
Replies
3
Views
295
Cubist
Cubist
J
  • Question Question
Stacked Clustered Column Chart
Replies
0
Views
273
johnnypp
J

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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