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

 

[NoSparks]

It will not allow me to post my screen shots

without knowing what you're actually working with assistance is unlikely to be offered.

It would be best to upload a sample workbook (without personal data) to a file sharing site like box.com or Dropbox
(both free to individual users) and share the link to it here.

 

[Peter_SSs]

Welcome to the MrExcel board!

It will not allow me to post my screen shots

In what way does it stop you? That is, what goes wrong?

I suggest that you investigate XL2BB for providing sample data.

 

[RBLuckett01]

Thank you so much for the suggestion @NoSparks for the box.com to share a link. I have attached the link with the calendar I am trying to update. I was able to tweak the calendar some by the help of AI but 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. The letters and numbers in the cells is what I want it to look like. Instead of me having to add each type of code manually. Where the green is, I have it set for conditional formatting from 0 to 71 will be GREEN, 72 to 119 will be YELLOW, and 120 & up will turn RED. It would be great for row B9 through NI9 to automatically calculate as well for each leave code. Because right now I am manually inputting. The leave breakup is for the year. The hours of leave taken can be from 1 hour to 24 hours because the department works 24 hour shifts and are off for 48 hours. I hope you can help. And thank you so much for the assistance on this.

Excel Leave Tracker 2021 - For FD Employees.xlsm | Powered by Box

app.box.com

 

[NoSparks]

Afraid that doesn't get me the file.
You need to copy the shared link and post it here.

 

[RBLuckett01]

Afraid that doesn't get me the file.
You need to copy the shared link and post it here.

Excel Leave Tracker 2021 - For FD Employees.xlsm | Powered by Box

app.box.com

I am sorry I hope this works

 

[NoSparks]

That worked, thanks

 

[Peter_SSs]

Thank you so much for the suggestion @NoSparks for the box.com to share a link.

Whilst using something like Box, One Drive etc may be useful at times, be aware that many of the helpers here choose not to download files from other sites or due to security restrictions at workplaces, are unable to download such files.

You will generally get more potential helpers if you explain your problem clearly in words and, if needed, post a small (copyable) screen shot or two directly in your post using XL2BB. My signature block below has help regarding that - see the XL2BB link.

 

[NoSparks]

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

 

[Peter_SSs]

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?