[Solved] Shift list with color problems

Discuss the spreadsheet application
Post Reply
DigiNorse
Posts: 1
Joined: Mon Apr 12, 2021 9:11 am

[Solved] Shift list with color problems

Post by DigiNorse »

Hello all you gurus :)
I have been banging at this for a few days now and cant seem to get anywhere.
Here is the case.
I am making a simple shift list with 8-9 employees and 4 different shifts, to make it easy to track shifts i want to assign each employee a set cell color along with the name. So that each shift that employee has is the same color.
I have tried Conditional Formats but that only allows me 3 "rules",I tried to make a list on a 2. sheet with all the wanted names and colors and then use a data and validation dropdown list but that just lets me choose names, and the colors do not show. What am i missing?
Up until now i have done this cell coloring manually, but after years of this i am getting sick of it ;:) Is there any help ?
(uploaded just an example to show what it looks like.)
Kind regards.
Attachments
EMPEX.ods
example file
(10.63 KiB) Downloaded 93 times
Last edited by MrProgrammer on Tue Apr 20, 2021 1:03 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.6 on Windows 10x64 Pro
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Shift list with color problems

Post by JohnSUN-Pensioner »

Create styles for each of the employees (in your example EMP1 ... EMP4) and use the STYLE() function in the conditional format.

CF_with_STYLE.gif

(YouTube)
Last edited by JohnSUN-Pensioner on Mon Apr 12, 2021 1:05 pm, edited 1 time in total.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Bill
Volunteer
Posts: 8932
Joined: Sat Nov 24, 2007 6:48 am

Re: Shift list with color problems

Post by Bill »

DigiNorse wrote:I have tried Conditional Formats but that only allows me 3 "rules"
LibreOffice doesn't have that limit. I tested up to 10 using LO 6.4.6.2.
AOO 4.1.14 on Ubuntu MATE 22.04
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Shift list with color problems

Post by MrProgrammer »

Hi, and welcome to the forum. Thank you for the attachment.
DigiNorse wrote:Up until now i have done this cell coloring manually, but after years of this i am getting sick of it ;:) Is there any help?
I would do this with the STYLE function. However I would not use conditional formatting because that feature does not play well with STYLE.

For your example I'd begin, as shown in JohnSUN-Pensioner's demonstration, with creating a style for each person. If you have a dozen people, you need to create a dozen styles. (Styles are needed for this task whether you use Conditional Formatting or not.) The New Style From Selection button makes this fairly easy. Then in a separate region of the sheet I would use the STYLE function to create the colors. Based on your example, where you have a grid of people in C3:F6, I would put formula =STYLE(C3) in cell H3 and press Enter. Using the fill handle fill the formula right and down into H3:K6. This will show a grid of zeros colored according to the styles. You only need to create the grid on the right once. You only need to create the styles once.

Select H3:K6, Edit → Copy, select C3, Edit → Paste Special → Deselect Paste All → Select only Formats→ OK.
This copies the styles (the colors) from H3:K6 to C3:F6.

If you change a value in the grid on the left, say setting D5 to EMP1, the color of D5 will not change. its color is set by the cell style, not by the cell value. However the color on the right (cell I5) will change. Thus you can repeat the steps in the paragraph above to refresh the updated styles (colors) to the left grid. I successfully tested this process.

Instead of using the Paste Special dialog, you can use PasteFormats from [Tutorial] Favorite Recorded Calc Macros if this is an action you will use often. I find that I do. You can even record the entire process of selecting H3:K6 and copying the formats to C3:F6. Then you make changes to the grid on the left and run your macro to fix the styles (colors).

If you need further assistance, you will get help more quickly if you attach the document with your formulas and data (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Explain in detail what actions you are taking and what you are having trouble with.
 Edit: Another approach is to use the STYLE function in the cells to set the color. Instead of putting EMP1 in the cell I can put =IF(STYLE("EMP1");{};"EMP1"). That is a nuisance to type, of course, so I can use two passes of Find & Replace. Say I change a cell from EMP1 to EMP4 by typing that value and pressing Enter. Then:
• Select C3:F6, Edit → Find & Replace → More options, select Current selection only and Regular Expressions, Search for ^[^"]+"([^"]+).+$, Replace with $1, Replace All.
• Select C3:F6, Edit → Find & Replace, Search for .+, Replace with =IF(STYLE("&");{};"&"), Replace All.

The first operation removes any existing STYLE formulas in the cells and converts them back to values. The second operation converts all the values to my STYLE formulas. I would record one macro to perform the two operations. You can store the macro in your spreadsheet.
Macro.png
 
 Edit: You could also use Data → Vaility to enter the STYLE formulas, though it will work better to use ="EMP1"&T(STYLE("EMP1")) than the IF() formula above. This seems much simpler than my earlier suggestions. 
 Edit: You can also create the "EMP1"&T(STYLE("EMP1")) formula as a defined name _EMP1, then use formula =_EMP1 in a Data → Validity → Cell Range list. 
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply