Linking Cells between sheets/workbooks

done.  you should be able to view them now.

sorry now its up there.

anyone having luck with this?

Don't know if this is helpful, but it's the best I can come up with on short notice.  Check out sheet 3.
You will have to change the commands (wenn = when; zählenwenn = count when; summe = sum; etc.).  I only have the german version.  This way:  you don't have to copy long formulas - only set up your personnel list once and link it dynamically to your input.
Hope this helps.
Rick

Will a macro work if it reformats it and put all the data in the second sheet for you, or do you have to use formulas?

Well, I didn't see the post the Rick made when I opened this question and made my last comment, so I don't know what he came up with...(but I will look after I post this)...but I've worked on this for a couple of hours, so I want to at least post it.

This is not an exact answer to your question, but it is a macro that I think does what you want.  It just takes all the data in sheet 1, and puts it into sheet 3 but formatted the way you had it on sheet2.
The only thing you will have to make sure of (because your original file was not like this) is that the Number in row 1 is in the 3rd column of each block of 4 columns (F1, J1, N1...etc...).  You can go as far over as you want, as long as they stay in that order, and the first name must be directly under it with the last name in the cell after the first name.  You can have as many charges for each name as you want.

Just download it and run the macro, then look at sheet3.  I don't know if you want to stay with using formulas, but if not, this macro does pretty good if I understand right what you want...There will be no more need to put formulas in anywhere...the macro puts formulas in the "total time" column to add up the 4 times, the rest of it is just data.

The only restriction I can see so far is that the format of the sheet has to be EXACTLY the same as in this file I am uploading (which is the same as what you meant yours to be anyway I think), and all the columns have to remain where they are now.

The only difference between your result sheet and this one, is that you had charges that went up to 5, even though there were only 3 for each name.  This only puts in as many charge rows as there are charges in the original data sheet, so if the original data sheet has 3 rows of charges for a particular name, then it only creates 3 charges in the final output sheet...(Sheet3)

Check it out...if you don't like it...discard it...if you need something different, let me know..

:-)
Albert

Here is the code and a working file..

Option Explicit
 
Sub ReArrange()
Dim r1 As Range
Dim MaxCol As Long
Dim MaxRow As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim r2 As Range
Dim fName As String
Dim lName As String
Dim iNum As Integer
Dim RowCnter As Integer
 
Set sh1 = Sheets("Sheet1") 'Sheet with data
Set sh2 = Sheets("Sheet3") 'Sheet to put the new data in
 
Application.ScreenUpdating = False
 
sh1.Select
 
 
Set r1 = sh1.Range("F1")
Set r2 = sh2.Range("A2")
 
sh2.Rows("2:10000").ClearContents
 
MaxCol = sh1.Cells(1, Cells.Columns.Count).End(xlToLeft).Column
 
 
 
Do Until r1.Column > MaxCol
If r1.Offset(1, 0).Value <> "" Then
    MaxRow = FindLast(sh1.Range(r1.Address).Offset(3, -2))
    If MaxRow > 3 Then
        iNum = r1.Value
        fName = r1.Offset(1, 0).Value
        lName = r1.Offset(1, 1).Value
            For RowCnter = 1 To MaxRow - 3
                r2.Value = iNum
                r2.Offset(0, 1).Value = fName
                r2.Offset(0, 2).Value = lName
                Set r2 = r2.Offset(1, 0)
            Next RowCnter
            
            'set r1 to first cell with the 4 times in data sheet
            Set r1 = r1.Offset(3, -2)
            
            
            'set r2 to first cell where times need to go in destination sheet
            Set r2 = r2.Offset(-(RowCnter - 1), 4)
            
            
            For RowCnter = 1 To MaxRow - 3
                Range(sh1.Cells(r1.Row, r1.Column), sh1.Cells(r1.Row, r1.Column + 3)).Copy
                r2.PasteSpecial xlPasteValues
                r2.Offset(0, 4).Formula = "=Sum(" & r2.Address & ":" & r2.Offset(0, 3).Address & ")"
                r2.Offset(0, -1).Value = r1.Offset(0, -(r1.Column - 3)).Value
                Set r1 = r1.Offset(1, 0)
                Set r2 = r2.Offset(1, 0)
            Next RowCnter
            Application.CutCopyMode = False
    End If
End If
            Set r1 = r1.Offset(-(r1.Row - 1), 6)
            Set r2 = r2.Offset(0, -4)
            
Loop
 
Application.ScreenUpdating = True
 
 
End Sub
 
Function FindLast(r As Range) As Long
    Dim MaxRow1 As Long
    Dim MaxRow2 As Long
    Dim MaxRow3 As Long
    Dim MaxRow4 As Long
    
    MaxRow1 = Cells(Cells.Rows.Count, r.Column).End(xlUp).Row
    MaxRow2 = Cells(Cells.Rows.Count, r.Offset(0, 1).Column).End(xlUp).Row
    MaxRow3 = Cells(Cells.Rows.Count, r.Offset(0, 2).Column).End(xlUp).Row
    MaxRow4 = Cells(Cells.Rows.Count, r.Offset(0, 3).Column).End(xlUp).Row
    
    FindLast = Application.WorksheetFunction.Max(MaxRow1, MaxRow2, MaxRow3, MaxRow4)
    
 
End Function
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:

ahammar - you have the right idea and your code works fine for the most part.  however the reason im avoiding macros is due to the fact that i dont know VBA and sending macro enabled files causes issues with the office personal.  So troubleshooting such problems seem to be very difficult at times.  So if there is anything up your sleeves with formulas, ill really appreiciate it.  For the most part ill leave this issue open for another few days and ill provide you points for all your hard work.  Please see what you can do in the mean time with using formulas only.  

Thanx

I totally understand.  I am a macro writing guru, but I am poor with formulas.  I do not have an answer for you as far as formulas...sorry...:-(

You do not have to give me points for my work unless it's just an easy way for you to close this question...In the rare events that I ask a question, I do that sometimes because it's just easier than writing to customer service and having it deleted.  Macros that do what my last one does are very challenging, and this was no exception...I enjoyed writing it so no big deal.  If you don't get an answer that you want, you are not obligated to accept any answer, and all of us experts know that (or should).

I do wish you good luck on finding an answer though...I just want people to get what they really want and like, not something that just "gets them by", so I hope you get a good answer...sorry I couldn't help with the formulas...

:-)
Albert

ahammar -
I know that macros are the best way out in such complex issues, but i found out one thing that the time it takes to fill these cells is too long.  i have 160.0000 rows that auto populate for my real version.  I guess excel offers more rows and columns but has not done anything for speeding up opening and calculating large files.  Mine is 41MB.  None the less thanx for ur efforts.  

huntersvcs:
I would appreciate it if you could put the solution up in english.  im not a programming person so for me to read code is the same as reading german, cant tell whats what.  but have it a go and lets see if your solution is more feasible.