Sorting data in all connected sheets without loosing information

X

xandria

New Member
Joined
Apr 13, 2018
Messages
3
Hey, I need help sorting out student attendance data.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; -webkit-text-stroke: #000000}span.s1 {font-kerning: none}</style>
For example, this is January Sheet.
Data is sorted by Column B (#ID)

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border:.5pt solid windowtext;}.xl64 {border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:none;}.xl65 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;}.xl66 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:left; vertical-align:middle; border-top:.5pt solid windowtext; border-right:none; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl67 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl68 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:none; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl69 {font-weight:700;}.xl70 {mso-number-format:"\[h\]\:mm"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl71 {mso-number-format:"\[h\]\:mm"; border:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl72 {mso-number-format:"\[h\]\:mm"; border-top:none; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl73 {mso-number-format:"\[h\]\:mm"; border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl74 {font-weight:700; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl75 {color:windowtext; font-weight:700; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl76 {font-weight:700; mso-number-format:"\[h\]\:mm"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl77 {font-weight:700; text-align:left; vertical-align:middle; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl78 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl79 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl80 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border-top:1.0pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:1.0pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl81 {mso-number-format:"Medium Time"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl82 {mso-number-format:"Medium Time"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}--></style>[TABLE="width: 780"]
<tbody>[TR]
[TD="class: xl77, width: 65"]Name[/TD]
[TD="class: xl74, width: 65"]#ID[/TD]
[TD="class: xl78, width: 195, colspan: 3"]Monday 01/01/18[/TD]
[TD="class: xl78, width: 195, colspan: 3"]Tuesday 01/02/18[/TD]
[TD="class: xl79, width: 195, colspan: 3"]Wednesday 01/03/18[/TD]
[TD="class: xl75, width: 65"]TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl66"]Caroline[/TD]
[TD="class: xl68"]01A[/TD]
[TD="class: xl81, align: right"]9:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl72, align: right"]7:00[/TD]
[TD="class: xl81, align: right"]9:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl72, align: right"]7:00[/TD]
[TD="class: xl81, align: right"]9:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl70, align: right"]7:00[/TD]
[TD="class: xl76, align: right"]21:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl67"]02A[/TD]
[TD="class: xl81, align: right"]10:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl73, align: right"]6:00[/TD]
[TD="class: xl81, align: right"]10:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl73, align: right"]6:00[/TD]
[TD="class: xl81, align: right"]10:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl71, align: right"]6:00[/TD]
[TD="class: xl76, align: right"]18:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Denize[/TD]
[TD="class: xl67"]03A[/TD]
[TD="class: xl81, align: right"]11:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl73, align: right"]5:00[/TD]
[TD="class: xl81, align: right"]11:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl73, align: right"]5:00[/TD]
[TD="class: xl81, align: right"]11:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl71, align: right"]5:00[/TD]
[TD="class: xl76, align: right"]15:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Alex[/TD]
[TD="class: xl68"]04A[/TD]
[TD="class: xl81, align: right"]9:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl72, align: right"]7:00[/TD]
[TD="class: xl81, align: right"]9:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl72, align: right"]7:00[/TD]
[TD="class: xl81, align: right"]9:00 AM[/TD]
[TD="class: xl82, align: right"]4:00 PM[/TD]
[TD="class: xl70, align: right"]7:00[/TD]
[TD="class: xl76, align: right"]21:00[/TD]
[/TR]
</tbody>[/TABLE]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; -webkit-text-stroke: #000000}span.s1 {font-kerning: none}</style>I have another sheet which sums up all hours for each student. Also sorted by Column B (#ID)

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border:.5pt solid windowtext;}.xl64 {border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:none;}.xl65 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;}.xl66 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:left; vertical-align:middle; border-top:.5pt solid windowtext; border-right:none; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl67 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl68 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:none; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl69 {font-weight:700;}.xl70 {font-weight:700; text-align:left; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl71 {font-weight:700; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl72 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl73 {color:windowtext; font-weight:700; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl74 {mso-number-format:"h\:mm\;\@"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl75 {mso-number-format:"Short Time"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl76 {font-weight:700; mso-number-format:"\[h\]\:mm"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}--></style>[TABLE="width: 715"]
<tbody>[TR]
[TD="class: xl70, width: 65"]Name[/TD]
[TD="class: xl71, width: 65"]#ID[/TD]
[TD="class: xl72, width: 65"]January[/TD]
[TD="class: xl72, width: 65"]February[/TD]
[TD="class: xl72, width: 65"]March[/TD]
[TD="class: xl72, width: 65"]April[/TD]
[TD="class: xl72, width: 65"]September[/TD]
[TD="class: xl72, width: 65"]October[/TD]
[TD="class: xl72, width: 65"]November[/TD]
[TD="class: xl72, width: 65"]December[/TD]
[TD="class: xl73, width: 65"]TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl66"]Caroline[/TD]
[TD="class: xl68"]01A[/TD]
[TD="class: xl74, align: right"] 21:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]17:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl76, align: right"]149:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl67"]02A[/TD]
[TD="class: xl74, align: right"]18:00[/TD]
[TD="class: xl75, align: right"]23:00[/TD]
[TD="class: xl75, align: right"]17:00[/TD]
[TD="class: xl75, align: right"]23:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]17:00[/TD]
[TD="class: xl75, align: right"]23:00[/TD]
[TD="class: xl75, align: right"]17:00[/TD]
[TD="class: xl76, align: right"]160:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Denize[/TD]
[TD="class: xl67"]03A[/TD]
[TD="class: xl74, align: right"]15:00[/TD]
[TD="class: xl75, align: right"]20:00[/TD]
[TD="class: xl75, align: right"]19:00[/TD]
[TD="class: xl75, align: right"]20:00[/TD]
[TD="class: xl75, align: right"]8:00[/TD]
[TD="class: xl75, align: right"]19:00[/TD]
[TD="class: xl75, align: right"]20:00[/TD]
[TD="class: xl75, align: right"]19:00[/TD]
[TD="class: xl76, align: right"]140:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Alex[/TD]
[TD="class: xl68"]04A[/TD]
[TD="class: xl74, align: right"]21:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]19:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl76, align: right"]151:00[/TD]
[/TR]
</tbody>[/TABLE]

How can I sort data alphabetically by student names so that monthly and total hours don't get messed up?

Thanks! ;)
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
xandria said:
Hey, I need help sorting out student attendance data.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; -webkit-text-stroke: #000000}span.s1 {font-kerning: none}</style>
For example, this is January Sheet.
Data is sorted by Column B (#ID)
<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border:.5pt solid windowtext;}.xl64 {border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:none;}.xl65 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;}.xl66 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:left; vertical-align:middle; border-top:.5pt solid windowtext; border-right:none; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl67 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl68 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:none; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl69 {font-weight:700;}.xl70 {mso-number-format:"\[h\]\:mm"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl71 {mso-number-format:"\[h\]\:mm"; border:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl72 {mso-number-format:"\[h\]\:mm"; border-top:none; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl73 {mso-number-format:"\[h\]\:mm"; border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl74 {font-weight:700; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl75 {color:windowtext; font-weight:700; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl76 {font-weight:700; mso-number-format:"\[h\]\:mm"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl77 {font-weight:700; text-align:left; vertical-align:middle; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl78 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl79 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl80 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border-top:1.0pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:1.0pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl81 {mso-number-format:"Medium Time"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl82 {mso-number-format:"Medium Time"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}--></style>[TABLE="width: 780"]
<tbody>[TR]
[TD="class: xl77, width: 65"]Name
[/TD]
[TD="class: xl74, width: 65"]#ID
[/TD]
[TD="class: xl78, width: 195, colspan: 3"]Monday 01/01/18
[/TD]
[TD="class: xl78, width: 195, colspan: 3"]Tuesday 01/02/18
[/TD]
[TD="class: xl79, width: 195, colspan: 3"]Wednesday 01/03/18
[/TD]
[TD="class: xl75, width: 65"]TOTAL
[/TD]
[/TR]
[TR]
[TD="class: xl66"]Caroline
[/TD]
[TD="class: xl68"]01A
[/TD]
[TD="class: xl81, align: right"]9:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl72, align: right"]7:00
[/TD]
[TD="class: xl81, align: right"]9:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl72, align: right"]7:00
[/TD]
[TD="class: xl81, align: right"]9:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl70, align: right"]7:00
[/TD]
[TD="class: xl76, align: right"]21:00
[/TD]
[/TR]
[TR]
[TD="class: xl66"]Ben
[/TD]
[TD="class: xl67"]02A
[/TD]
[TD="class: xl81, align: right"]10:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl73, align: right"]6:00
[/TD]
[TD="class: xl81, align: right"]10:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl73, align: right"]6:00
[/TD]
[TD="class: xl81, align: right"]10:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl71, align: right"]6:00
[/TD]
[TD="class: xl76, align: right"]18:00
[/TD]
[/TR]
[TR]
[TD="class: xl66"]Denize
[/TD]
[TD="class: xl67"]03A
[/TD]
[TD="class: xl81, align: right"]11:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl73, align: right"]5:00
[/TD]
[TD="class: xl81, align: right"]11:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl73, align: right"]5:00
[/TD]
[TD="class: xl81, align: right"]11:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl71, align: right"]5:00
[/TD]
[TD="class: xl76, align: right"]15:00
[/TD]
[/TR]
[TR]
[TD="class: xl66"]Alex
[/TD]
[TD="class: xl68"]04A
[/TD]
[TD="class: xl81, align: right"]9:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl72, align: right"]7:00
[/TD]
[TD="class: xl81, align: right"]9:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl72, align: right"]7:00
[/TD]
[TD="class: xl81, align: right"]9:00 AM
[/TD]
[TD="class: xl82, align: right"]4:00 PM
[/TD]
[TD="class: xl70, align: right"]7:00
[/TD]
[TD="class: xl76, align: right"]21:00
[/TD]
[/TR]
</tbody>[/TABLE]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; -webkit-text-stroke: #000000}span.s1 {font-kerning: none}</style>I have another sheet which sums up all hours for each student. Also sorted by Column B (#ID)

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border:.5pt solid windowtext;}.xl64 {border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:none;}.xl65 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;}.xl66 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:left; vertical-align:middle; border-top:.5pt solid windowtext; border-right:none; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl67 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl68 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:none; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl69 {font-weight:700;}.xl70 {font-weight:700; text-align:left; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl71 {font-weight:700; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl72 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl73 {color:windowtext; font-weight:700; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl74 {mso-number-format:"h\:mm\;\@"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl75 {mso-number-format:"Short Time"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl76 {font-weight:700; mso-number-format:"\[h\]\:mm"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}--></style>[TABLE="width: 715"]
<tbody>[TR]
[TD="class: xl70, width: 65"]Name
[/TD]
[TD="class: xl71, width: 65"]#ID
[/TD]
[TD="class: xl72, width: 65"]January
[/TD]
[TD="class: xl72, width: 65"]February
[/TD]
[TD="class: xl72, width: 65"]March
[/TD]
[TD="class: xl72, width: 65"]April
[/TD]
[TD="class: xl72, width: 65"]September
[/TD]
[TD="class: xl72, width: 65"]October
[/TD]
[TD="class: xl72, width: 65"]November
[/TD]
[TD="class: xl72, width: 65"]December
[/TD]
[TD="class: xl73, width: 65"]TOTAL
[/TD]
[/TR]
[TR]
[TD="class: xl66"]Caroline
[/TD]
[TD="class: xl68"]01A
[/TD]
[TD="class: xl74, align: right"] 21:00
[/TD]
[TD="class: xl75, align: right"]22:00
[/TD]
[TD="class: xl75, align: right"]15:00
[/TD]
[TD="class: xl75, align: right"]22:00
[/TD]
[TD="class: xl75, align: right"]17:00
[/TD]
[TD="class: xl75, align: right"]15:00
[/TD]
[TD="class: xl75, align: right"]22:00
[/TD]
[TD="class: xl75, align: right"]15:00
[/TD]
[TD="class: xl76, align: right"]149:00
[/TD]
[/TR]
[TR]
[TD="class: xl66"]Ben
[/TD]
[TD="class: xl67"]02A
[/TD]
[TD="class: xl74, align: right"]18:00
[/TD]
[TD="class: xl75, align: right"]23:00
[/TD]
[TD="class: xl75, align: right"]17:00
[/TD]
[TD="class: xl75, align: right"]23:00
[/TD]
[TD="class: xl75, align: right"]22:00
[/TD]
[TD="class: xl75, align: right"]17:00
[/TD]
[TD="class: xl75, align: right"]23:00
[/TD]
[TD="class: xl75, align: right"]17:00
[/TD]
[TD="class: xl76, align: right"]160:00
[/TD]
[/TR]
[TR]
[TD="class: xl66"]Denize
[/TD]
[TD="class: xl67"]03A
[/TD]
[TD="class: xl74, align: right"]15:00
[/TD]
[TD="class: xl75, align: right"]20:00
[/TD]
[TD="class: xl75, align: right"]19:00
[/TD]
[TD="class: xl75, align: right"]20:00
[/TD]
[TD="class: xl75, align: right"]8:00
[/TD]
[TD="class: xl75, align: right"]19:00
[/TD]
[TD="class: xl75, align: right"]20:00
[/TD]
[TD="class: xl75, align: right"]19:00
[/TD]
[TD="class: xl76, align: right"]140:00
[/TD]
[/TR]
[TR]
[TD="class: xl66"]Alex
[/TD]
[TD="class: xl68"]04A
[/TD]
[TD="class: xl74, align: right"]21:00
[/TD]
[TD="class: xl75, align: right"]22:00
[/TD]
[TD="class: xl75, align: right"]15:00
[/TD]
[TD="class: xl75, align: right"]22:00
[/TD]
[TD="class: xl75, align: right"]19:00
[/TD]
[TD="class: xl75, align: right"]15:00
[/TD]
[TD="class: xl75, align: right"]22:00
[/TD]
[TD="class: xl75, align: right"]15:00
[/TD]
[TD="class: xl76, align: right"]151:00
[/TD]
[/TR]
</tbody>[/TABLE]

How can I sort data alphabetically by student names so that monthly and total hours don't get messed up?

Thanks! ;)
Click to expand...

If you do not have any formulas with external references, you should be able to select the entire range of data with Range("A1") designated as your Sort Key and all of the other columns will automatically be kept with the respective Name in column A.
 
Last edited:
Upvote 0
Excel sorts out January sheets nicely. But "All Hours" sheet gets messed up. I used simple SUM formulas to sum up hours.
 
Upvote 0
xandria said:
Excel sorts out January sheets nicely. But "All Hours" sheet gets messed up. I used simple SUM formulas to sum up hours.
Click to expand...

I copied your table and deleted the totals data, made sure that all cells with numbers and the total column was formatted as hh:mm and then inserted
Code: 
=Sum(C2:J2)
in cell K2 and dragged down through row 5 in column K. I then selected the entire table with cell A1 as the active cell and clicked Sort A to Z and it sorted correctly with the totals hour being displayed correctly for each name.

[TABLE="width: 749"]
<colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 2746;"> <col width="77" style="width: 58pt;"> <col width="78" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2257;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 2676;"> <col width="78" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2257;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2350;"> <col width="98" style="width: 73pt; mso-width-source: userset; mso-width-alt: 2839;"> <col width="78" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2257;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 2769;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3025;"> <col width="126" style="width: 94pt; mso-width-source: userset; mso-width-alt: 3653;"> <tbody>[TR]
[TD="class: xl64, width: 94, bgcolor: white"]Name[/TD]
[TD="class: xl64, width: 77, bgcolor: white"]#ID[/TD]
[TD="class: xl64, width: 78, bgcolor: white"]January[/TD]
[TD="class: xl64, width: 92, bgcolor: white"]February[/TD]
[TD="class: xl64, width: 78, bgcolor: white"]March[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]April[/TD]
[TD="class: xl64, width: 98, bgcolor: white"]September[/TD]
[TD="class: xl64, width: 78, bgcolor: white"]October[/TD]
[TD="class: xl64, width: 95, bgcolor: white"]November[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]December[/TD]
[TD="class: xl64, width: 126, bgcolor: white"]TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 94, bgcolor: white"]Alex[/TD]
[TD="class: xl64, width: 77, bgcolor: white"]04A[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]21:00[/TD]
[TD="class: xl65, width: 92, bgcolor: white"]22:00[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]15:00[/TD]
[TD="class: xl65, width: 81, bgcolor: white"]22:00[/TD]
[TD="class: xl65, width: 98, bgcolor: white"]19:00[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]15:00[/TD]
[TD="class: xl65, width: 95, bgcolor: white"]22:00[/TD]
[TD="class: xl65, width: 104, bgcolor: white"]15:00[/TD]
[TD="class: xl66, width: 126, bgcolor: white"]151:00:00[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 94, bgcolor: white"]Ben[/TD]
[TD="class: xl64, width: 77, bgcolor: white"]02A[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]18:00[/TD]
[TD="class: xl65, width: 92, bgcolor: white"]23:00[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]17:00[/TD]
[TD="class: xl65, width: 81, bgcolor: white"]23:00[/TD]
[TD="class: xl65, width: 98, bgcolor: white"]22:00[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]17:00[/TD]
[TD="class: xl65, width: 95, bgcolor: white"]23:00[/TD]
[TD="class: xl65, width: 104, bgcolor: white"]17:00[/TD]
[TD="class: xl66, width: 126, bgcolor: white"]160:00:00[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 94, bgcolor: white"]Caroline[/TD]
[TD="class: xl64, width: 77, bgcolor: white"]01A[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]21:00[/TD]
[TD="class: xl65, width: 92, bgcolor: white"]22:00[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]15:00[/TD]
[TD="class: xl65, width: 81, bgcolor: white"]22:00[/TD]
[TD="class: xl65, width: 98, bgcolor: white"]17:00[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]15:00[/TD]
[TD="class: xl65, width: 95, bgcolor: white"]22:00[/TD]
[TD="class: xl65, width: 104, bgcolor: white"]15:00[/TD]
[TD="class: xl66, width: 126, bgcolor: white"]149:00:00[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 94, bgcolor: white"]Denize[/TD]
[TD="class: xl64, width: 77, bgcolor: white"]03A[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]15:00[/TD]
[TD="class: xl65, width: 92, bgcolor: white"]20:00[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]19:00[/TD]
[TD="class: xl65, width: 81, bgcolor: white"]20:00[/TD]
[TD="class: xl65, width: 98, bgcolor: white"]8:00[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]19:00[/TD]
[TD="class: xl65, width: 95, bgcolor: white"]20:00[/TD]
[TD="class: xl65, width: 104, bgcolor: white"]19:00[/TD]
[TD="class: xl66, width: 126, bgcolor: white"]140:00:00[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
These sheets work very well individually. But for example if I sort data A to Z in "January" sheet. And then go to sheet "All Hours" - hours per name will not match.

JANUARY

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border:.5pt solid windowtext;}.xl64 {border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:none;}.xl65 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;}.xl66 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:left; vertical-align:middle; border-top:.5pt solid windowtext; border-right:none; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl67 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl68 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:none; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl69 {font-weight:700;}.xl70 {mso-number-format:"\[h\]\:mm"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl71 {mso-number-format:"\[h\]\:mm"; border:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl72 {mso-number-format:"\[h\]\:mm"; border-top:none; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl73 {mso-number-format:"\[h\]\:mm"; border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl74 {font-weight:700; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl75 {color:windowtext; font-weight:700; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl76 {font-weight:700; mso-number-format:"\[h\]\:mm"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl77 {font-weight:700; text-align:left; vertical-align:middle; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl78 {mso-number-format:"Medium Time"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl79 {mso-number-format:"Medium Time"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl80 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl81 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl82 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border-top:1.0pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:1.0pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl83 {font-weight:700; mso-number-format:"Short Time"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}--></style>[TABLE="width: 780"]
<!--StartFragment--> <colgroup><col width="65" span="11" style="width:65pt"> <col width="65" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl77, width: 65"]Name[/TD]
[TD="class: xl74, width: 65"]#ID[/TD]
[TD="class: xl80, width: 195, colspan: 3"]Monday 01/01/18[/TD]
[TD="class: xl80, width: 195, colspan: 3"]Tuesday 01/02/18[/TD]
[TD="class: xl81, width: 195, colspan: 3"]Wednesday 01/03/18[/TD]
[TD="class: xl75, width: 65"]TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl66"]Alex[/TD]
[TD="class: xl68"]04A[/TD]
[TD="class: xl78, align: right"]9:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl72, align: right"]7:00[/TD]
[TD="class: xl78, align: right"]9:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl72, align: right"]7:00[/TD]
[TD="class: xl78, align: right"]9:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl70, align: right"]7:00[/TD]
[TD="class: xl76, align: right"]21:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl67"]02A[/TD]
[TD="class: xl78, align: right"]10:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl73, align: right"]6:00[/TD]
[TD="class: xl78, align: right"]10:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl73, align: right"]6:00[/TD]
[TD="class: xl78, align: right"]10:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl71, align: right"]6:00[/TD]
[TD="class: xl76, align: right"]18:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Caroline[/TD]
[TD="class: xl67"]01A[/TD]
[TD="class: xl78, align: right"]9:00 AM[/TD]
[TD="class: xl79, align: right"]12:00 PM[/TD]
[TD="class: xl73, align: right"]3:00[/TD]
[TD="class: xl78, align: right"]9:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl73, align: right"]7:00[/TD]
[TD="class: xl78, align: right"]9:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl71, align: right"]7:00[/TD]
[TD="class: xl83, align: right"]17:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Denize[/TD]
[TD="class: xl68"]03A[/TD]
[TD="class: xl78, align: right"]11:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl72, align: right"]5:00[/TD]
[TD="class: xl78, align: right"]11:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl72, align: right"]5:00[/TD]
[TD="class: xl78, align: right"]11:00 AM[/TD]
[TD="class: xl79, align: right"]4:00 PM[/TD]
[TD="class: xl70, align: right"]5:00[/TD]
[TD="class: xl76, align: right"]15:00[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

ALL HOURS

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border:.5pt solid windowtext;}.xl64 {border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:none;}.xl65 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;}.xl66 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:left; vertical-align:middle; border-top:.5pt solid windowtext; border-right:none; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl67 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl68 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border-top:none; border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl69 {font-weight:700;}.xl70 {font-weight:700; text-align:left; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl71 {font-weight:700; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl72 {font-weight:700; mso-number-format:"dddd\\ mm\/dd\/yy"; text-align:center; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl73 {color:windowtext; font-weight:700; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; text-align:center; vertical-align:middle; border:1.0pt solid windowtext; background:white; mso-pattern:black none;}.xl74 {mso-number-format:"h\:mm\;\@"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl75 {mso-number-format:"Short Time"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none; background:white; mso-pattern:black none;}.xl76 {font-weight:700; mso-number-format:"\[h\]\:mm"; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; background:white; mso-pattern:black none;}--></style>[TABLE="width: 715"]
<!--StartFragment--> <colgroup><col width="65" span="10" style="width:65pt"> <col width="65" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl70, width: 65"]Name[/TD]
[TD="class: xl71, width: 65"]#ID[/TD]
[TD="class: xl72, width: 65"]January[/TD]
[TD="class: xl72, width: 65"]February[/TD]
[TD="class: xl72, width: 65"]March[/TD]
[TD="class: xl72, width: 65"]April[/TD]
[TD="class: xl72, width: 65"]September[/TD]
[TD="class: xl72, width: 65"]October[/TD]
[TD="class: xl72, width: 65"]November[/TD]
[TD="class: xl72, width: 65"]December[/TD]
[TD="class: xl73, width: 65"]TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl66"]Caroline[/TD]
[TD="class: xl68"]01A[/TD]
[TD="class: xl74, align: right"]21:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]17:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl76, align: right"]149:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl67"]02A[/TD]
[TD="class: xl74, align: right"]18:00[/TD]
[TD="class: xl75, align: right"]23:00[/TD]
[TD="class: xl75, align: right"]17:00[/TD]
[TD="class: xl75, align: right"]23:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]17:00[/TD]
[TD="class: xl75, align: right"]23:00[/TD]
[TD="class: xl75, align: right"]17:00[/TD]
[TD="class: xl76, align: right"]160:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Denize[/TD]
[TD="class: xl67"]03A[/TD]
[TD="class: xl74, align: right"]17:00[/TD]
[TD="class: xl75, align: right"]20:00[/TD]
[TD="class: xl75, align: right"]19:00[/TD]
[TD="class: xl75, align: right"]20:00[/TD]
[TD="class: xl75, align: right"]8:00[/TD]
[TD="class: xl75, align: right"]19:00[/TD]
[TD="class: xl75, align: right"]20:00[/TD]
[TD="class: xl75, align: right"]19:00[/TD]
[TD="class: xl76, align: right"]142:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]Alex[/TD]
[TD="class: xl68"]04A[/TD]
[TD="class: xl74, align: right"]15:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]19:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl75, align: right"]22:00[/TD]
[TD="class: xl75, align: right"]15:00[/TD]
[TD="class: xl76, align: right"]145:00[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]



For example Alex has now 15 h in January month instead of 21h. So in JANUARY sheet that is L2 cell but in ALL HOURS sheet - L5
 
Upvote 0
That is because the precedent cells the formula uses no longer applies to that name once the other table is sorted. You might be able to do what you want by using named ranges for the cells and use that name in the formula rather than a cell reference. The named range will retain its identity even after sorting.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
E
  • Solved
Match sets of values
Replies
9
Views
1K
Akuini
Akuini
B
  • Question Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs
D
  • Question Question
How to output a dynamic range where the height of the range varies
Replies
0
Views
1K
dagda13
D
N
  • Question Question
INDEX MATCH duplicate value issues
Replies
3
Views
1K
newbie37
N

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

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