Totaling Hours by Code
Solved/Closed
nowlanandrea
Posts
7
Registration date
Wednesday February 15, 2017
Status
Member
Last seen
February 28, 2017
-
Updated by nowlanandrea on 16/02/17 at 08:06 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Feb 17, 2017 at 08:59 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Feb 17, 2017 at 08:59 AM
Related:
- Totaling Hours by Code
- How to get whatsapp verification code by email - Guide
- Samsung keypad reset code - Guide
- Battery reset code - Guide
- Samsung volume increase code - Guide
- Usa country code for whatsapp - Guide
4 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Feb 16, 2017 at 11:43 AM
Feb 16, 2017 at 11:43 AM
Andrea, Good afternoon.
I do not know why your spreadsheet layout is this, but probably another type of layout would greatly facilitate you working with payroll data.
I assembled the formulas taking into account exactly this layout.
A formula was made and duplicated for the other days of the week.
You may notice that each formula consists of 5 similar parts changing only the interval references.
Perhaps there is a more elegant way to do this formula.
That's the way I got it now.
Make sure the formula meets your need.
https://www.sendspace.com/file/in5h94
I hope I've helped
I do not know why your spreadsheet layout is this, but probably another type of layout would greatly facilitate you working with payroll data.
I assembled the formulas taking into account exactly this layout.
A formula was made and duplicated for the other days of the week.
You may notice that each formula consists of 5 similar parts changing only the interval references.
Perhaps there is a more elegant way to do this formula.
That's the way I got it now.
Make sure the formula meets your need.
https://www.sendspace.com/file/in5h94
I hope I've helped
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Feb 16, 2017 at 01:17 PM
Feb 16, 2017 at 01:17 PM
Andrea,
"...I tried to just copy and paste the formula but I cannot get it to work, any advice?..."
Yes!
Possibly when you copy the formula from my example worksheet and paste it into your actual worksheet, the copy is bearing the address of the original worksheet: [16_Fev_2017_Worksheet-OK.xls]
This should be the cause of the error.
IF, the other worksheets really have the same layout as the one you sent me to solve, then the CODES tab formula, cell B2 is this:
= IFERROR(INDEX('SECTION 2'!$F$44:$Q$57, MATCH(B$1, 'SECTION 2'!$B$44:$B$57, 0), MATCH($A2, 'SECTION 2'!$F$43:$Q$43, 0)), 0) + IFERROR(INDEX('SECTION 2'!$F$65:$Q$77, MATCH(B$1, 'SECTION 2'!$B$65:$B$77, 0), MATCH($A2, 'SECTION 2'!$F$64:$Q$64, 0)), 0) + IFERROR(INDEX('SECTION 2'!$F$86:$Q$98, MATCH(B$1, 'SECTION 2'!$B$86:$B$98, 0), MATCH($A2, 'SECTION 2'!$F$85:$Q$85, 0)), 0) + IFERROR(INDEX('SECTION 2'!$F$107:$Q$119, MATCH(B$1, 'SECTION 2'!$B$107:$B$119, 0), MATCH($A2, 'SECTION 2'!$F$106:$Q$106, 0)), 0) + IFERROR(INDEX('SECTION 2'!$F$128:$Q$140, MATCH(B$1, 'SECTION 2'!$B$128:$B$140, 0), MATCH($A2, 'SECTION 2'!$F$127:$Q$127, 0)), 0)
This is the only original formula.
All others will be derived from this.
Try to do:
Simply Copy the above formula;
Paste in ABA CODES cell B2;
Select Cell B2 and Copy;
Select cells B2:H33;
Special Paste --> Formulas.
The magic is done.
Please tell us if it worked for you.
I really hope I have helped you.
--
Belo Horizonte, Brasil.
Marcílio Lobão
"...I tried to just copy and paste the formula but I cannot get it to work, any advice?..."
Yes!
Possibly when you copy the formula from my example worksheet and paste it into your actual worksheet, the copy is bearing the address of the original worksheet: [16_Fev_2017_Worksheet-OK.xls]
This should be the cause of the error.
IF, the other worksheets really have the same layout as the one you sent me to solve, then the CODES tab formula, cell B2 is this:
= IFERROR(INDEX('SECTION 2'!$F$44:$Q$57, MATCH(B$1, 'SECTION 2'!$B$44:$B$57, 0), MATCH($A2, 'SECTION 2'!$F$43:$Q$43, 0)), 0) + IFERROR(INDEX('SECTION 2'!$F$65:$Q$77, MATCH(B$1, 'SECTION 2'!$B$65:$B$77, 0), MATCH($A2, 'SECTION 2'!$F$64:$Q$64, 0)), 0) + IFERROR(INDEX('SECTION 2'!$F$86:$Q$98, MATCH(B$1, 'SECTION 2'!$B$86:$B$98, 0), MATCH($A2, 'SECTION 2'!$F$85:$Q$85, 0)), 0) + IFERROR(INDEX('SECTION 2'!$F$107:$Q$119, MATCH(B$1, 'SECTION 2'!$B$107:$B$119, 0), MATCH($A2, 'SECTION 2'!$F$106:$Q$106, 0)), 0) + IFERROR(INDEX('SECTION 2'!$F$128:$Q$140, MATCH(B$1, 'SECTION 2'!$B$128:$B$140, 0), MATCH($A2, 'SECTION 2'!$F$127:$Q$127, 0)), 0)
This is the only original formula.
All others will be derived from this.
Try to do:
Simply Copy the above formula;
Paste in ABA CODES cell B2;
Select Cell B2 and Copy;
Select cells B2:H33;
Special Paste --> Formulas.
The magic is done.
Please tell us if it worked for you.
I really hope I have helped you.
--
Belo Horizonte, Brasil.
Marcílio Lobão
nowlanandrea
Posts
7
Registration date
Wednesday February 15, 2017
Status
Member
Last seen
February 28, 2017
Feb 17, 2017 at 08:53 AM
Feb 17, 2017 at 08:53 AM
Thank you thank you thank you!!!
You have helped me tremendously!
Have a great day!
Andrea
You have helped me tremendously!
Have a great day!
Andrea
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Feb 16, 2017 at 08:04 AM
Feb 16, 2017 at 08:04 AM
Andrea, Good morning.
Try to save your file at any free site, www.sendspace.com , dropbox.com , ge.tt and put a link here to download.
It will easier things for people that are trying help you.
Try to save your file at any free site, www.sendspace.com , dropbox.com , ge.tt and put a link here to download.
It will easier things for people that are trying help you.
nowlanandrea
Posts
7
Registration date
Wednesday February 15, 2017
Status
Member
Last seen
February 28, 2017
Feb 16, 2017 at 08:06 AM
Feb 16, 2017 at 08:06 AM
Thank you!
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Feb 17, 2017 at 08:59 AM
Feb 17, 2017 at 08:59 AM
Andrea, Good morning
Thanks for the feedback.
Glad to have helped you.
Have a nice day!
--
Belo Horizonte, Brasil.
Marcílio Lobão
Thanks for the feedback.
Glad to have helped you.
Have a nice day!
--
Belo Horizonte, Brasil.
Marcílio Lobão
Feb 16, 2017 at 12:00 PM
My only problem now is trying to transfer this formula to another workbook - I have 3 of these sheets every week, in the same format that I am trying to get this to work in.
I tried to just copy and paste the formula but I cannot get it to work, any advice?
Thank you again! Great work!
Andrea