Adding hidden values to a text and having them add up in a total box

New Contributor

Hi Guys, I'm a newby to advanced excel. I am doing rostering and I want to assign shifts to my employees but I don't want the hours visible. So they will be assigned as below. 





I would like the shift codes to have an underlying value and add up to the amount of daily hours in the total box, then I want the hours to be carried over to another sheet with the weeks hours.


weekly hours.png


Does this make sense?


Can someone help me please?


Employee shift schedule1.xlsx



2 Replies
best response confirmed by Hans Vogelaar (MVP)

Hi @Shell78 


- Renamed your Tables

- Added a ShiftCode/Hours Table

- Implemented formula to match Shift codes & Hours in daily Tables

- Implemented formula to report daily totals in Employee sheet


Updated workbook attached


@L z. Thank you so much! It's perfect! 


I really appreciate you doing them for me. I would really like to know how you did it?