SOLVED
Home

Create 'incell' 5 day gant with diff colors to show office, home but working, not available

%3CLINGO-SUB%20id%3D%22lingo-sub-320461%22%20slang%3D%22en-US%22%3ECreate%20'incell'%205%20day%20gant%20with%20diff%20colors%20to%20show%20office%2C%20home%20but%20working%2C%20not%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320461%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3EI'd%20like%20to%20create%20an%20overall%20manpower%20chart%20with%20coloured%20symbols%20to%20give%20an%20at%20a%20glance%20idea%20of%20whether%20the%20staff%20is%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20working%20from%20office%20(dark%20green)%3C%2FP%3E%3CP%3E2.%20working%20from%20home%20(light%20green)%3C%2FP%3E%3CP%3E3.%20not%20available.%3C%2FP%3E%3CP%3ECurrently%20i%20have%20a%20helper%20cell%20where%20i%20can%20quickly%20type%20out%20%22O%22%2C%20%22H%22%2C%20%22X%22%20but%20i'd%20like%20my%20display%20cell%20to%20convert%20this%20into%20an%20appropriate%20coloured%20block%20which%20is%20visually%20more%20appealing.%3C%2FP%3E%3CP%3ETypical%20weeks%20might%20be%3C%2FP%3E%3CP%3E1.%20OOOOO%20-%20working%20in%20office%20for%205%20days%26nbsp%3B-%20solid%20dark%20green%20block%3C%2FP%3E%3CP%3E2.%20HHOOX%20-%20work%20two%20days%20from%20home%2C%20two%20days%20in%20office%20and%20not%20available%20-%20Light%20green%20x%202%20blocks%2C%20Dark%20green%20x%202%20blocks%20and%20a%20red%20block.%3C%2FP%3E%3CP%3EVisual%20example%20attached.%3C%2FP%3E%3CP%3ECurrently%20i%20am%20formatting%20the%20display%20cell%20manually%2C%20but%20as%20staff%20movement%20changes%20its%20quite%20tedious.%3C%2FP%3E%3CP%3EI%20would%20have%20done%20a%20unique%20cell%20for%20each%20day%20but%20it%20makes%20for%20a%20long%20table%20so%20went%20with%20the%20incell%20approach.%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20637px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68957iC19157C1FAD5271C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-320461%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Egannt%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Egant%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eprogram%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eprogramme%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320818%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20'incell'%205%20day%20gant%20with%20diff%20colors%20to%20show%20office%2C%20home%20but%20working%2C%20not%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320818%22%20slang%3D%22en-US%22%3EThanks%20N%2C%3CBR%20%2F%3EAs%20Sergei%20mention%2C%20its%205%20diff%20colors%20in%201%20cell%20to%20represent%20the%20week.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320753%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20'incell'%205%20day%20gant%20with%20diff%20colors%20to%20show%20office%2C%20home%20but%20working%2C%20not%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320753%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20I%20understood%20that%20shall%20be%205%20different%20colors%20blocks%20in%26nbsp%3BONE%20cell%20which%20covers%20entire%20week.%20One%20cell%20-%20one%20week%20-%205%20colors.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320749%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20'incell'%205%20day%20gant%20with%20diff%20colors%20to%20show%20office%2C%20home%20but%20working%2C%20not%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320749%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20create%20something%20like%20the%20attached%20and%20you%20won't%20need%20to%20have%20helper%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320735%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20'incell'%205%20day%20gant%20with%20diff%20colors%20to%20show%20office%2C%20home%20but%20working%2C%20not%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320735%22%20slang%3D%22en-US%22%3E%3CP%3EI%20guess%20that's%20only%20with%20VBA%20programming.%20As%20variant%20the%20code%20will%20analyse%20the%20cell%20below%2C%20depends%20on%20combination%20of%20symbols%20combine%20Wingding%20font%20blocks%20with%20their%20own%20colors%20into%20one%20text%20and%20return%20it%20to%20your%20cell%2C%20formatting%20it%26nbsp%3Bas%20Wingding.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320577%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20'incell'%205%20day%20gant%20with%20diff%20colors%20to%20show%20office%2C%20home%20but%20working%2C%20not%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320577%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Sergei.%3C%2FP%3E%3CP%3EI%20was%20avoiding%20putting%20each%20day%20in%20a%20column%20since%20i%20have%3C%2FP%3E%3CP%3E1.%20other%20references%20in%20cells%20above%20which%20breaks%20up%20into%20non%20adjacent%20cells%3C%2FP%3E%3CP%3E2.%20there%20are%20numerous%20days%20that%20i%20have%20to%20consider%20in%20the%20programme.%3C%2FP%3E%3CP%3ECan%20you%20suggest%20any%20other%20way%20that%20makes%20in%20cell%20with%205%20block%20symbols%20to%20indicate%20days...%20that%20would%20really%20help!%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3CP%3EM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320476%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20'incell'%205%20day%20gant%20with%20diff%20colors%20to%20show%20office%2C%20home%20but%20working%2C%20not%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320476%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20conditional%20formatting%20rule%20could%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20564px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68958i4128979BB9660DDF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20key%20point%2C%20if%20your%20range%20starts%20from%20B1%20in%20the%20rule%20you%20shall%20use%20B2%20(next%20row%20and%20relative%20references).%20Please%20check%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
manosh
New Contributor

Hello!

I'd like to create an overall manpower chart with coloured symbols to give an at a glance idea of whether the staff is 

1. working from office (dark green)

2. working from home (light green)

3. not available.

Currently i have a helper cell where i can quickly type out "O", "H", "X" but i'd like my display cell to convert this into an appropriate coloured block which is visually more appealing.

Typical weeks might be

1. OOOOO - working in office for 5 days - solid dark green block

2. HHOOX - work two days from home, two days in office and not available - Light green x 2 blocks, Dark green x 2 blocks and a red block.

Visual example attached.

Currently i am formatting the display cell manually, but as staff movement changes its quite tedious.

I would have done a unique cell for each day but it makes for a long table so went with the incell approach.

Any help would be appreciated.

Capture.JPG

6 Replies
Highlighted

Hi,

 

The conditional formatting rule could be like

image.png

The key point, if your range starts from B1 in the rule you shall use B2 (next row and relative references). Please check attached.

Highlighted

Thanks Sergei.

I was avoiding putting each day in a column since i have

1. other references in cells above which breaks up into non adjacent cells

2. there are numerous days that i have to consider in the programme.

Can you suggest any other way that makes in cell with 5 block symbols to indicate days... that would really help!

thanks

M

Highlighted
Solution

I guess that's only with VBA programming. As variant the code will analyse the cell below, depends on combination of symbols combine Wingding font blocks with their own colors into one text and return it to your cell, formatting it as Wingding.

Highlighted

Hi,

 

You can create something like the attached and you won't need to have helper rows.

 

Hope that helps.

 

Kind regards

 

Highlighted

As I understood that shall be 5 different colors blocks in ONE cell which covers entire week. One cell - one week - 5 colors.

Highlighted
Thanks N,
As Sergei mention, its 5 diff colors in 1 cell to represent the week.
Related Conversations
Office Deployment: Pin to START Menu
Brian LeFlem in Microsoft Intune on
1 Replies
I can't open a Macro enable excel and word file
Roxanne26 in Office 365 on
2 Replies
Content and files in office.com not loading
Nomnomburger25 in Discussions on
3 Replies
Trouble with group calendar in Outlook
angela_GTME in Office 365 Groups on
1 Replies
Access Takes a Trip: News From Around the Globe
Ebo_Quansah in Access on
0 Replies