hey guys, glad to know there are wonderful forums for random questions like this. i'm not bad at excel, but my goodness am i ever stumped right now.
i have in a workbook (weekending date whatever) MANY sheets (per job) in which i have a list of men's names and occupation codes (Lastname, Firstname | Pipefitter), then the columns SUN OT | MON REG | MON OT | TUES REG | TUES OT | etc.)
On one of the last sheets in the workbook i have a totalling sheet where it (should) grab each man's hours from each sheet and total per day, then a total for the week.
The issue i am having is because i have tables holding the information on each sheet, the totalling sheet doesn't hold the hours to the correct man if we sort alphabetically Z-A or mix it up any. On each particular job sheet if we sort Z-A or A-Z the hours remain with the man's name. Example, on job sheet 1, if someone with the lastname A has 4 hours on SUN OT, and then we sort Z-A, those 4 hours would be near the bottom of the list... but on the job totalling sheet it also moves to the bottom of the sheet even though the names remain in alphabetical order
So, upon this perdicament, I began googling. I came up with this DREADFUL formula that doesn't even work. Essentially, what i was trying to get the cell to do is to total all the "SUN OT" for example, IF $A$6 was the same as the sheet it found it on? does that make sense? does that resolve the problem? I don't know :S
Feel free to call me any name in the book. There are literally, like, 50ish job sheets. LOL. Maybe its time for the loony bin. in anycase, HELP!!!
this is what happened:
=SUMIFS((Table211[[#This Row],[SUN OT]]+Table011[[#This Row],[SUN OT]]+Table3711[[#This Row],[SUN OT]]+Table111[[#This Row],[SUN OT]]+Table3811[[#This Row],[SUN OT]]+Table3911[[#This Row],[SUN OT]]+Table4011[[#This Row],[SUN OT]]+Table9917[[#This Row],[SUN OT]]+Table9916[[#This Row],[SUN OT]]+Table9915[[#This Row],[SUN OT]]+Table9914[[#This Row],[SUN OT]]+Table9913[[#This Row],[SUN OT]]+Table9912[[#This Row],[SUN OT]]+Table31134[[#This Row],[SUN OT]]+Table311[[#This Row],[SUN OT]]+Table1111[[#This Row],[SUN OT]]+Table411[[#This Row],[SUN OT]]+Table511[[#This Row],[SUN OT]]+Table611[[#This Row],[SUN OT]]+Table811[[#This Row],[SUN OT]]+Table711[[#This Row],[SUN OT]]+Table911[[#This Row],[SUN OT]]+Table1011[[#This Row],[SUN OT]]+Table1411[[#This Row],[SUN OT]]+Table1511[[#This Row],[SUN OT]]+Table1611[[#This Row],[SUN OT]]+Table2911[[#This Row],[SUN OT]]+Table4111[[#This Row],[SUN OT]]+Table1711[[#This Row],[SUN OT]]+Table1811[[#This Row],[SUN OT]]+Table1911[[#This Row],[SUN OT]]+Table2011[[#This Row],[SUN OT]]+Table2111[[#This Row],[SUN OT]]+Table2211[[#This Row],[SUN OT]]+Table1311[[#This Row],[SUN OT]]+Table3611[[#This Row],[SUN OT]]+Table2311[[#This Row],[SUN OT]]+Table2411[[#This Row],[SUN OT]]+Table2511[[#This Row],[SUN OT]]+Table2611[[#This Row],[SUN OT]]+Table2711[[#This Row],[SUN OT]]+Table2811[[#This Row],[SUN OT]]+Table3011[[#This Row],[SUN OT]]+Table3111[[#This Row],[SUN OT]]+Table4211[[#This Row],[SUN OT]]+Table3211[[#This Row],[SUN OT]]+Table3311[[#This Row],[SUN OT]]+Table3411[[#This Row],[SUN OT]]+Table3511[[#This Row],[SUN OT]]+Table4311[[#This Row],[SUN OT]]+Table4411[[#This Row],[SUN OT]]+Table4511[[#This Row],[SUN OT]]+Table4611[[#This Row],[SUN OT]]+Table4711[[#This Row],[SUN OT]]+Table4811[[#This Row],[SUN OT]]+Table4911[[#This Row],[SUN OT]]+Table5011[[#This Row],[SUN OT]]),Table11264[[#This Row],[NAME]],'Names & Codes'!A6:A700)
i have in a workbook (weekending date whatever) MANY sheets (per job) in which i have a list of men's names and occupation codes (Lastname, Firstname | Pipefitter), then the columns SUN OT | MON REG | MON OT | TUES REG | TUES OT | etc.)
On one of the last sheets in the workbook i have a totalling sheet where it (should) grab each man's hours from each sheet and total per day, then a total for the week.
The issue i am having is because i have tables holding the information on each sheet, the totalling sheet doesn't hold the hours to the correct man if we sort alphabetically Z-A or mix it up any. On each particular job sheet if we sort Z-A or A-Z the hours remain with the man's name. Example, on job sheet 1, if someone with the lastname A has 4 hours on SUN OT, and then we sort Z-A, those 4 hours would be near the bottom of the list... but on the job totalling sheet it also moves to the bottom of the sheet even though the names remain in alphabetical order
So, upon this perdicament, I began googling. I came up with this DREADFUL formula that doesn't even work. Essentially, what i was trying to get the cell to do is to total all the "SUN OT" for example, IF $A$6 was the same as the sheet it found it on? does that make sense? does that resolve the problem? I don't know :S
Feel free to call me any name in the book. There are literally, like, 50ish job sheets. LOL. Maybe its time for the loony bin.
in anycase, HELP!!!this is what happened:
=SUMIFS((Table211[[#This Row],[SUN OT]]+Table011[[#This Row],[SUN OT]]+Table3711[[#This Row],[SUN OT]]+Table111[[#This Row],[SUN OT]]+Table3811[[#This Row],[SUN OT]]+Table3911[[#This Row],[SUN OT]]+Table4011[[#This Row],[SUN OT]]+Table9917[[#This Row],[SUN OT]]+Table9916[[#This Row],[SUN OT]]+Table9915[[#This Row],[SUN OT]]+Table9914[[#This Row],[SUN OT]]+Table9913[[#This Row],[SUN OT]]+Table9912[[#This Row],[SUN OT]]+Table31134[[#This Row],[SUN OT]]+Table311[[#This Row],[SUN OT]]+Table1111[[#This Row],[SUN OT]]+Table411[[#This Row],[SUN OT]]+Table511[[#This Row],[SUN OT]]+Table611[[#This Row],[SUN OT]]+Table811[[#This Row],[SUN OT]]+Table711[[#This Row],[SUN OT]]+Table911[[#This Row],[SUN OT]]+Table1011[[#This Row],[SUN OT]]+Table1411[[#This Row],[SUN OT]]+Table1511[[#This Row],[SUN OT]]+Table1611[[#This Row],[SUN OT]]+Table2911[[#This Row],[SUN OT]]+Table4111[[#This Row],[SUN OT]]+Table1711[[#This Row],[SUN OT]]+Table1811[[#This Row],[SUN OT]]+Table1911[[#This Row],[SUN OT]]+Table2011[[#This Row],[SUN OT]]+Table2111[[#This Row],[SUN OT]]+Table2211[[#This Row],[SUN OT]]+Table1311[[#This Row],[SUN OT]]+Table3611[[#This Row],[SUN OT]]+Table2311[[#This Row],[SUN OT]]+Table2411[[#This Row],[SUN OT]]+Table2511[[#This Row],[SUN OT]]+Table2611[[#This Row],[SUN OT]]+Table2711[[#This Row],[SUN OT]]+Table2811[[#This Row],[SUN OT]]+Table3011[[#This Row],[SUN OT]]+Table3111[[#This Row],[SUN OT]]+Table4211[[#This Row],[SUN OT]]+Table3211[[#This Row],[SUN OT]]+Table3311[[#This Row],[SUN OT]]+Table3411[[#This Row],[SUN OT]]+Table3511[[#This Row],[SUN OT]]+Table4311[[#This Row],[SUN OT]]+Table4411[[#This Row],[SUN OT]]+Table4511[[#This Row],[SUN OT]]+Table4611[[#This Row],[SUN OT]]+Table4711[[#This Row],[SUN OT]]+Table4811[[#This Row],[SUN OT]]+Table4911[[#This Row],[SUN OT]]+Table5011[[#This Row],[SUN OT]]),Table11264[[#This Row],[NAME]],'Names & Codes'!A6:A700)
