[Solved] Set up conditional formatting

Discuss the spreadsheet application
Post Reply
swmwshrk
Posts: 22
Joined: Fri Aug 15, 2014 3:56 am

[Solved] Set up conditional formatting

Post by swmwshrk »

Here is what I need, can't figure it out.

Column A is name of a project.
Column B is most current version #
Columns C-L are servers where the project is stored that do not back up automatically or are outside my direct control.

I need a way to compare each project's current number to the number in column C-L and find out if they DO NOT match. If they match all is well. If not, I need to know.

I also need a way to jump from unmatched to unmatched versions without scanning the whole sheet with my mark 1 mod 0 eyeball.

Right now, there are over 300 projects listed with more being added every few days.
Some older projects don't yet have version numbers so null values are valid.
Others have the same version number since we use a date code as the version number.
Some projects are not on all servers, so N/A is a valid value as well.

I tried an if-then in conditional formatting but couldn't get it to work. Please and thank you for your help.
Last edited by swmwshrk on Mon Oct 11, 2021 2:33 am, edited 1 time in total.
I am very new to excel/calc. What I do know so far is self taught and very limited, I still think formulas are a form of black magic.Please give me step by step instructions, and explain abbreviations. Thank you for your help

Win 7 pro Open Office 4.1.3
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Set up conditional formatting

Post by FJCC »

Please upload a file with a few examples of the data you need to handle. It should include examples of the cases you mention, such as N/A and null. Do you want to flag every cell where there is a problem or just the row?

To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
swmwshrk
Posts: 22
Joined: Fri Aug 15, 2014 3:56 am

Set up conditional formatting

Post by swmwshrk »

I am doing this from home. The file is at work on the intranet. I cannot copy or upload it.

Each row is a different item so

A1 Project Alpha B1 v456 C1 v456 D1 Not Auth E1 v455 F1 (Empty) G1 NV# H1 v456 I1 000 J1 (EMPTY)

A2 Project Beta B2 v456 C2 v456 D2 Not Auth E2 v455 F2 (Empty) G2 NV# H2 v456 I2 000 J2 (EMPTY)


C-L only verify in their row. C3 Checks B3 only C4 checks B4 only etc...

In the example above I want E1 F1 G1 I1 and J1 highlighted. Same for row 2.

Cell values can be
Version number, If C-L Match B1, do nothing. If any cell in the row does not match the V# in B1 highlight that cell.
Not authorized Take no action, that item is not allowed/Needed on this server
empty cell/0.0/NV# all mean that the file on that server does not have a version number. Not all current items have a version # this is something new we started doing and assign V#'s when we update the project. We are in the process of standardizing the data but have to reconcile info from several places and this is currently a low priority, manual process.

I also don't wan't other formatting to change Justifications, fonts etc.. I tried doing this and stuff changes I don't want to change. When I try it either highlights everything in the row of mess up the formatting. The only thing I want is a reverse format, black cell white font when they don't match.
I am very new to excel/calc. What I do know so far is self taught and very limited, I still think formulas are a form of black magic.Please give me step by step instructions, and explain abbreviations. Thank you for your help

Win 7 pro Open Office 4.1.3
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Set up conditional formatting

Post by FJCC »

The attached file has an example of how I would do this. I applied manual formatting to E1 and G2 and it is preserved when the conditional formatting is applied.
Attachments
CondFormat.ods
(9.06 KiB) Downloaded 102 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Post Reply