SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3294570%22%20slang%3D%22en-US%22%3EAdding%20hidden%20values%20to%20a%20text%20and%20having%20them%20add%20up%20in%20a%20total%20box%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3294570%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%20I'm%20a%20newby%20to%20advanced%20excel.%20I%20am%20doing%20rostering%20and%20I%20want%20to%20assign%20shifts%20to%20my%20employees%20but%20I%20don't%20want%20the%20hours%20visible.%20So%20they%20will%20be%20assigned%20as%20below.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Shell78_0-1650932041220.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F366734iBCC0232C3DAC347B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Shell78_0-1650932041220.png%22%20alt%3D%22Shell78_0-1650932041220.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20the%20shift%20codes%20to%20have%20an%20underlying%20value%20and%20add%20up%20to%20the%20amount%20of%20daily%20hours%20in%20the%20total%20box%2C%20then%20I%20want%20the%20hours%20to%20be%20carried%20over%20to%20another%20sheet%20with%20the%20weeks%20hours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22weekly%20hours.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F366742i248490CBF791931F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22weekly%20hours.png%22%20alt%3D%22weekly%20hours.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20this%20make%20sense%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AiwNo2pggGBtpUaotYU3tEDZsS6s%3Fe%3D5xVCwZ%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EEmployee%20shift%20schedule1.xlsx%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShell7%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3294570%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300027%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20hidden%20values%20to%20a%20text%20and%20having%20them%20add%20up%20in%20a%20total%20box%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300027%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much!%20It's%20perfect!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20appreciate%20you%20doing%20them%20for%20me.%20I%20would%20really%20like%20to%20know%20how%20you%20did%20it%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3294780%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20hidden%20values%20to%20a%20text%20and%20having%20them%20add%20up%20in%20a%20total%20box%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3294780%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1373065%22%20target%3D%22_blank%22%3E%40Shell78%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Renamed%20your%20Tables%3C%2FP%3E%3CP%3E-%20Added%20a%20ShiftCode%2FHours%20Table%3C%2FP%3E%3CP%3E-%20Implemented%20formula%20to%20match%20Shift%20codes%20%26amp%3B%20Hours%20in%20daily%20Tables%3C%2FP%3E%3CP%3E-%20Implemented%20formula%20to%20report%20daily%20totals%20in%20Employee%20sheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUpdated%20workbook%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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. 

 

Shell78_0-1650932041220.png

 

 

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

 

Shell7

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

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?