Trying to change the formula in this excel sheet with calendar

[RBLuckett01]

I am trying to change the formula on the second row for the Employees to sum the actual leave taken for the month in column NJ and then total up for the year in column NK. But this formula and data need to change through each page. The sheet is hanged to a different month by the arrows at the top of the excel page.

Secondly, I would also like to change the color of the leave breakup to match the leave taken for that month. So, green is 0-71 hours used, yellow is 72-119 hours used, red is 120-100000 hours used. I already have column NJ and NK setup that way to change the colors but I would also like the the letters to change as well if possible.

EDIT:
=SUMPRODUCT((OFFSET($A8,0,31*($A$3-1)+1,1,31)<>"")*(IF(OFFSET($A8,0,31*($A$3-1)+1,1,31)=Sheet1!$B$29,0.5,IF(OFFSET($A8,0,31*($A$3-1)+1,1,31)=Sheet1!$B$30,0.5,1))*(OFFSET($A$4,0,31*($A$3-1)+1,1,31)))) Month formula

=SUMPRODUCT((OFFSET($A8,0,1,1,372)<>"")*(IF(OFFSET($A8,0,1,1,372)=Sheet1!$B$29,0.5,IF(OFFSET($A8,0,1,1,372)=Sheet1!$B$30,0.5,1))*(OFFSET($A$3,0,1,1,372)))) Year formula

It will not allow me to post my screen shots

 

[RBLuckett01]

Sorry RB, afraid those formulas are a little too much for me.
Hopefully another member will be able to assist.
Good luck with the project

Thank you for trying.

 

[RBLuckett01]

It is very hard to see what is happening with your worksheet and what the formulas are supposed to be doing. However, it looks to me like your use of the volatile function OFFSET is excessive and probably not needed at all or at least in a vastly reduced manner.

In words, what are the formulas in B3:NI3 supposed to be doing?

It is very hard to see what is happening with your worksheet and what the formulas are supposed to be doing. However, it looks to me like your use of the volatile function OFFSET is excessive and probably not needed at all or at least in a vastly reduced manner.

In words, what are the formulas in B3:NI3 supposed to be doing

Thank you for trying.

It is very hard to see what is happening with your worksheet and what the formulas are supposed to be doing. However, it looks to me like your use of the volatile function OFFSET is excessive and probably not needed at all or at least in a vastly reduced manner.

In words, what are the formulas in B3:NI3 supposed to be doing?

Peter,

This calendar was pulled from this site - Free Excel Leave Tracker Template (Updated for 2023) I just added information pertaining to my department and was trying to adjust some formulas but did not work. I am not sure what those formulas control but I do know if they are deleted it does create an error for the sheet. It states holiday in the formula so I am assuming the formulas control the holidays listed on the tab listed holidays.

 

[Peter_SSs]

This calendar was pulled from this site

Thanks for that information. I think there are some poor formulas and design in that workbook but given its complexity and the fact that you can't help with explaining what certain formulas are doing, I am not prepared to spend the enormous amount of time that would be required to re-formulate it for a free public forum like this. Sorry.

 

[RBLuckett01]

Thanks for that information. I think there are some poor formulas and design in that workbook but given its complexity and the fact that you can't help with explaining what certain formulas are doing, I am not prepared to spend the enormous amount of time that would be required to re-formulate it for a free public forum like this. Sorry.

Thank you for reviewing but I wasnt asking you or anyone else to reformulate the entire workbook and especially for FREE. Thank you again for reviewing and have a great day.

 

[NoSparks]

Hello RBLuckett01

I first saw this scrolling calendar a couple years ago .
Since then have seen a few request for assistance with it on the different forums and always played with it each time.
Your post here, and Peter's take on the formulas inspired me to have another "kick at the cat" with what I've accumulated along the way.

If you're interested, here's what I've done

changed cell formats ;;; to general
removed formulas from rows 3-7
deleted the veryhidden sheet
created a user form to put real dates into rows 5 & 6 and Week # in row 7
row 1 is hidden and holds the info needed for the scrolling
the user form says data will be wiped out but the line of code that does that is commented out

formula now in NJ8 for 'this month' =SUMPRODUCT(($B8:$NI8<>"")*(MONTH($B$5:$NI$5)=$C$1))
formula now in NK8 for 'this year' =COUNTA($B8:$NI8)

I didn't touch any of the 449 conditional formatting rules on that sheet.
I suspect you might want to do a little clean-up with that.

Was fun playing with this, hope it is of some use.
Good luck with your project
NoSparks

 

[Peter_SSs]

I wasnt asking you or anyone else to reformulate the entire workbook

Yes, I understand that.

I would like for the green rows to be automatically updated, if possible. I want to be able to create a formula that will calculate each leave code thats placed in cells starting at B8 through NI8 and the leave taken thats placed in cells B9 through NI9 to be totaled automatically under the correspondent leave code in cells NL9 though NU9.

Did you mean for the currently displayed month only?
Try this, in NL9 of a copy of your workbook, copied across. This formula row can then be copied to rows 11, 13, 15, ...

=SUMIFS($B9:$NI9,$B$6:$NI$6,"><",$B8:$NI8,NL$5)

or for the whole year?

=SUMIFS($B9:$NI9,$B8:$NI8,NL$5)

 

[RBLuckett01]

Hello RBLuckett01

I first saw this scrolling calendar a couple years ago .
Since then have seen a few request for assistance with it on the different forums and always played with it each time.
Your post here, and Peter's take on the formulas inspired me to have another "kick at the cat" with what I've accumulated along the way.

If you're interested, here's what I've done

changed cell formats ;;; to general
removed formulas from rows 3-7
deleted the veryhidden sheet
created a user form to put real dates into rows 5 & 6 and Week # in row 7
row 1 is hidden and holds the info needed for the scrolling
the user form says data will be wiped out but the line of code that does that is commented out

formula now in NJ8 for 'this month' =SUMPRODUCT(($B8:$NI8<>"")*(MONTH($B$5:$NI$5)=$C$1))
formula now in NK8 for 'this year' =COUNTA($B8:$NI8)

I didn't touch any of the 449 conditional formatting rules on that sheet.
I suspect you might want to do a little clean-up with that.

Was fun playing with this, hope it is of some use.
Good luck with your project
NoSparks

Thank you so much

 

[RBLuckett01]

Yes, I understand that.


Did you mean for the currently displayed month only?
Try this, in NL9 of a copy of your workbook, copied across. This formula row can then be copied to rows 11, 13, 15, ...

=SUMIFS($B9:$NI9,$B$6:$NI$6,"><",$B8:$NI8,NL$5)

or for the whole year?

=SUMIFS($B9:$NI9,$B8:$NI8,NL$5)

Thank you so much that worked. Exactly what I was looking for.

 

[Peter_SSs]

Thank you so much that worked. Exactly what I was looking for.

You are welcome. Thanks for the follow-up.

 

[Peter_SSs]

@RBLuckett01
I notice that you had marked post #10 as the solution to this thread. Clearly it is not, so I have removed that mark. If one of the other posts does contain a solution for you then please mark that post, otherwise it is fine if no post is marked. This will help future readers who may be looking for a similar answer.