Sorting data in all connected sheets without loosing information

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>
Name#IDMonday 01/01/18Tuesday 01/02/18Wednesday 01/03/18TOTAL
Caroline01A9:00 AM4:00 PM7:009:00 AM4:00 PM7:009:00 AM4:00 PM7:0021:00
Ben02A10:00 AM4:00 PM6:0010:00 AM4:00 PM6:0010:00 AM4:00 PM6:0018:00
Denize03A11:00 AM4:00 PM5:0011:00 AM4:00 PM5:0011:00 AM4:00 PM5:0015:00
Alex04A9:00 AM4:00 PM7:009:00 AM4:00 PM7:009:00 AM4:00 PM7:0021:00

<tbody>
</tbody>

<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>
Name#IDJanuaryFebruaryMarchAprilSeptemberOctoberNovemberDecemberTOTAL
Caroline01A 21:0022:0015:0022:0017:0015:0022:0015:00149:00
Ben02A18:0023:0017:0023:0022:0017:0023:0017:00160:00
Denize03A15:0020:0019:0020:008:0019:0020:0019:00140:00
Alex04A21:0022:0015:0022:0019:0015:0022:0015:00151:00

<tbody>
</tbody>

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

Thanks! ;)
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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>
Name
#ID
Monday 01/01/18
Tuesday 01/02/18
Wednesday 01/03/18
TOTAL
Caroline
01A
9:00 AM
4:00 PM
7:00
9:00 AM
4:00 PM
7:00
9:00 AM
4:00 PM
7:00
21:00
Ben
02A
10:00 AM
4:00 PM
6:00
10:00 AM
4:00 PM
6:00
10:00 AM
4:00 PM
6:00
18:00
Denize
03A
11:00 AM
4:00 PM
5:00
11:00 AM
4:00 PM
5:00
11:00 AM
4:00 PM
5:00
15:00
Alex
04A
9:00 AM
4:00 PM
7:00
9:00 AM
4:00 PM
7:00
9:00 AM
4:00 PM
7:00
21:00

<tbody>
</tbody>

<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>
Name
#ID
January
February
March
April
September
October
November
December
TOTAL
Caroline
01A
21:00
22:00
15:00
22:00
17:00
15:00
22:00
15:00
149:00
Ben
02A
18:00
23:00
17:00
23:00
22:00
17:00
23:00
17:00
160:00
Denize
03A
15:00
20:00
19:00
20:00
8:00
19:00
20:00
19:00
140:00
Alex
04A
21:00
22:00
15:00
22:00
19:00
15:00
22:00
15:00
151:00

<tbody>
</tbody>

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

Thanks! ;)

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:

xandria

New Member
Joined
Apr 13, 2018
Messages
3
Excel sorts out January sheets nicely. But "All Hours" sheet gets messed up. I used simple SUM formulas to sum up hours.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Excel sorts out January sheets nicely. But "All Hours" sheet gets messed up. I used simple SUM formulas to sum up hours.

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.

Name#IDJanuaryFebruaryMarchAprilSeptemberOctoberNovemberDecemberTOTAL
Alex04A21:0022:0015:0022:0019:0015:0022:0015:00151:00:00
Ben02A18:0023:0017:0023:0022:0017:0023:0017:00160:00:00
Caroline01A21:0022:0015:0022:0017:0015:0022:0015:00149:00:00
Denize03A15:0020:0019:0020:008:0019:0020:0019:00140:00:00
<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> </tbody>
 
Last edited:

xandria

New Member
Joined
Apr 13, 2018
Messages
3
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>
Name#IDMonday 01/01/18Tuesday 01/02/18Wednesday 01/03/18TOTAL
Alex04A9:00 AM4:00 PM7:009:00 AM4:00 PM7:009:00 AM4:00 PM7:0021:00
Ben02A10:00 AM4:00 PM6:0010:00 AM4:00 PM6:0010:00 AM4:00 PM6:0018:00
Caroline01A9:00 AM12:00 PM3:009:00 AM4:00 PM7:009:00 AM4:00 PM7:0017:00
Denize03A11:00 AM4:00 PM5:0011:00 AM4:00 PM5:0011:00 AM4:00 PM5:0015:00

<!--StartFragment--> <colgroup><col width="65" span="11" style="width:65pt"> <col width="65" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

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>
Name#IDJanuaryFebruaryMarchAprilSeptemberOctoberNovemberDecemberTOTAL
Caroline01A21:0022:0015:0022:0017:0015:0022:0015:00149:00
Ben02A18:0023:0017:0023:0022:0017:0023:0017:00160:00
Denize03A17:0020:0019:0020:008:0019:0020:0019:00142:00
Alex04A15:0022:0015:0022:0019:0015:0022:0015:00145:00

<!--StartFragment--> <colgroup><col width="65" span="10" style="width:65pt"> <col width="65" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>



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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,579
Messages
5,637,209
Members
416,961
Latest member
sigrid6940

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
Top