Hi SirJB7,
I'm very thankful for your effort and time.
It is perfectly fine which you prepare with help of formulas.
Please check that there is any other way to do it with easy way like macros or any other procedures.
I've one macro which i'm using (got it from chandoo.org), please look at it and loop this one up to last cost center in data sheet.
=======================
Sub TransferData()
Dim LastRow As Integer
'Where is the last cell with data?
LastRow = Worksheets("410").Range("A65536").End(xlUp).Row
LastRow = Worksheets("513").Range("A65536").End(xlUp).Row
LastRow = Worksheets("514").Range("A65536").End(xlUp).Row
LastRow = Worksheets("515").Range("A65536").End(xlUp).Row
LastRow = Worksheets("518").Range("A65536").End(xlUp).Row
LastRow = Worksheets("519").Range("A65536").End(xlUp).Row
LastRow = Worksheets("537").Range("A65536").End(xlUp).Row
LastRow = Worksheets("538").Range("A65536").End(xlUp).Row
LastRow = Worksheets("541").Range("A65536").End(xlUp).Row
LastRow = Worksheets("542").Range("A65536").End(xlUp).Row
LastRow = Worksheets("543").Range("A65536").End(xlUp).Row
LastRow = Worksheets("544").Range("A65536").End(xlUp).Row
LastRow = Worksheets("545").Range("A65536").End(xlUp).Row
LastRow = Worksheets("547").Range("A65536").End(xlUp).Row
LastRow = Worksheets("554").Range("A65536").End(xlUp).Row
LastRow = Worksheets("613").Range("A65536").End(xlUp).Row
LastRow = Worksheets("616").Range("A65536").End(xlUp).Row
LastRow = Worksheets("617").Range("A65536").End(xlUp).Row
LastRow = Worksheets("627").Range("A65536").End(xlUp).Row
LastRow = Worksheets("646").Range("A65536").End(xlUp).Row
LastRow = Worksheets("665").Range("A65536").End(xlUp).Row
LastRow = Worksheets("711").Range("A65536").End(xlUp).Row
LastRow = Worksheets("715").Range("A65536").End(xlUp).Row
LastRow = Worksheets("904").Range("A65536").End(xlUp).Row
'Transfer data
Sheets("data").Range("a1:d100").Copy Worksheets("410").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,e1:g100").Copy Worksheets("513").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,h1:j100").Copy Worksheets("514").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,k1:m100").Copy Worksheets("515").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,n1

100").Copy Worksheets("518").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,q1:s100").Copy Worksheets("519").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,t1:v100").Copy Worksheets("537").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,w1:y100").Copy Worksheets("538").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,z1:ab100").Copy Worksheets("541").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,ac1:ae100").Copy Worksheets("542").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,af1:ah100").Copy Worksheets("543").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,ai1:ak100").Copy Worksheets("544").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,aL1:aN100").Copy Worksheets("545").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,aO1:aQ100").Copy Worksheets("547").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,aR1:aT100").Copy Worksheets("554").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,aU1:aW100").Copy Worksheets("613").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,aX1:aZ100").Copy Worksheets("616").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,BA1:BC100").Copy Worksheets("617").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,BD1:BF100").Copy Worksheets("627").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,bG1:bI100").Copy Worksheets("646").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,BJ1:BL100").Copy Worksheets("665").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,BM1:BO100").Copy Worksheets("711").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,BP1:BR100").Copy Worksheets("715").Cells(LastRow + 1, "A")
Sheets("data").Range("a1:a100,BS1:BU100").Copy Worksheets("904").Cells(LastRow + 1, "A")
End Sub
===========================
Thanks-Sekhar