Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Running total that resets

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Running total that resets

    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.

  2. #2
    Board Regular synergy16's Avatar
    Join Date
    Mar 2016
    Location
    new england
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running total that resets

    i think i can help you here, but i need more clarification and what exactly you need
    Last edited by Fluff; May 17th, 2019 at 01:02 PM.

  3. #3
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    534
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running total that resets

    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.

  4. #4
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    534
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running total that resets

    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

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,987
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Running total that resets

    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:



     ABCDEFGHIJ
    1          
    2174a100mirror       
    3174b62mirror       
    4175a51 51AB 3021 
    5175b33 84BC 924 
    6175c1 85C  1  
    7178a25 25A  25  
    8178b21 46AB 516 
    9178c52 98BCD14308
    10178d13 111D  13  
    11178e11 122DE 92 
    12178f17 139E  17  
    13178g54 193EF 113013
    14178h50 243FG 17303
    15178i3 246G  3  
    16173a26 26A  26  
    17179a60 60AB 3030 
    18179b57 117CD 3027 
    19179c60 177DEF33027
    20179d60 237FGH33027
    21179e26 263HI  323 
    22179f4 267I  4  
    23179g60 327IJK33027
    24179h59 386KLM33026


    Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
    Regards Dante Amor

  6. #6
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,654
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Running total that resets

    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:
    Sub MG19May03
    Dim Rng As Range, Dn As Range, tot As Long, c As Long, Num As Long, R As Range, n As Long
    Dim nRay() As Variant, Ac As Long, nTot As Long
    Set Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
        If Not Dn.Offset(, 2).Value = "mirror" Then
            If Not .Exists(Left(Dn.Value, 3)) Then
                .Add Left(Dn.Value, 3), Dn.Offset(, 1)
            Else
                Set .Item(Left(Dn.Value, 3)) = Union(.Item(Left(Dn.Value, 3)), Dn.Offset(, 1))
        End If
    End If
    Next
    
    
    Dim K As Variant, p As Long, M As Long, Ray As Variant, G As Long
    For Each K In .keys
      tot = 0: Num = 65
      ReDim Ray(1 To Application.Sum(.Item(K)), 1 To 2)
    
      For Each R In .Item(K)
        For 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
        Next n
    Next R
    
    
    M = 1
    c = 0
    For n = 1 To UBound(Ray, 1)
        If Ray(n, 1) = .Item(K)(M) Then
            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))
        ElseIf Ray(n, 2) = 30 Then
                c = c + 1
                G = G + 1
                ReDim Preserve nRay(1 To G)
                nRay(G) = Application.Min(Ray(n, 1), Ray(n, 2))
        End If
    Next n
    
    Num = 65: c = 0
    For Each Dn In .Item(K)
        Ac = 2: tot = 0
            Do 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
                If nTot Mod 30 = 0 Then Num = Num + 1
            Loop
    Next Dn
    
    Erase nRay: c = 0: tot = 0: nTot = 0: p = 0: G = 0
    Next K
    End With
    Regards Mick

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,987
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Running total that resets

    Quote Originally Posted by MickG View Post
    My Calculations seems to be at odds with rows 13,14 & 15 of your data.
    Okay, I also have difference in the result with the example in those rows.
    Regards Dante Amor

  8. #8
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,654
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Running total that resets

    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. !!!

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,987
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Running total that resets

    Hi Mike:

    I understood that the OP wanted the numbers
    Regards Dante Amor

  10. #10
    Board Regular
    Join Date
    Aug 2016
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running total that resets

    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.
    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



    Worksheet Formulas
    CellFormula
    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)

    Array Formulas
    CellFormula
    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),"")}
    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))}
    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),"")}
    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))}
    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),"")}
    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))}
    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),"")}
    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))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Workbook Defined Names
    NameRefers To
    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 by Lobsterboy1; May 20th, 2019 at 01:08 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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