Openoffice conditional formatting based on match

#Openoffice conditional formatting based on match how to
#Openoffice conditional formatting based on match code
#Openoffice conditional formatting based on match download

As certain conditions are met the intent is to lock out previously filled cells to prevent them from being overwritten. The spreadsheet is being used by others to input incremental data over time. I apologize for being away over the weekend and not clarifying myself further. If the initial protection is to protect against malicious intent, then this is entirely the wrong way to go about that.Дavid. I frankly don't see any other way, and see no reason why the person entering the data then protecting can not do it this way. The secondary protection is to stop any further data entry. The initial protection is to stop data entry into areas not intended. You enter that data then protect the entire sheet from anyone and everyone else? Why would someone intentionally misuse a spreadsheet if they were to be allowed to do that further protection? So far as I can see, my solution works for that purpose. I set up a spreadsheet with cells protected except for some of data entry. Entering wrong data is intentional misuse for example. There is nothing that can be done about intentional misuse if they can enter data. The zipped file is in xlsx format (or xls format for Excel 2003), and does not contain any macros.Kingfisher wrote:The objective is to set up the spreadsheet for others to use and to build in some protection against their accidental or intentional misuse.

#Openoffice conditional formatting based on match download

Scroll down to the Download section, and click the link to get the workbook. Go to the Conditional Formatting Examples page on my Contextures website. To see how this conditional formatting works, you can download the sample file. A relative reference is used for the data entry cell (D5), because it should adjust to match each cell where the conditional formatting is applied.Absolute references are used for $B$2, $E$2 and $F$2 because no matter where the conditional formatting is applied, it should always check those cells.Some of the references are Absolute, and one is Relative: Does the data entry cell (D5) match the condition entered in cell F2Д¬ell D5 is used in the formula, because that was the active cell when the conditional formatting was applied.Does cell B2 match the condition entered in cell E2.The AND function checks the 2 conditions: The conditional formatting formula is: =AND($B$2=$E$2,D5=$F$2)

Click OK, to apply the conditional formattingД«ecause вЂњUSвЂќ is entered in cell B2, any cell in D5:D14 that contains вЂњUnited StatesвЂќ is coloured red.

Select red as the fill colour, and click OK.

Click Use a Formula to Determine Which Cells to Format.

On the RibbonвЂ™s Home tab, click Conditional Formatting, then click New Rule.Select cells D5:D14, where the country names are listed for the orders.WeвЂ™ll use the AND function, to check both conditions, and the formula is explained in the next section.

The next step is to add conditional formatting to the country cells in the data range D5:D14.

#Openoffice conditional formatting based on match code

People will type a country code in cell B2, so I filled that cell with yellow, to make it stand out. You could also name the cells, and use those names in the conditional formatting formula. You could put them on a different sheet, if you prefer, to prevent people from accidentally changing them. In this workbook, the conditions are in cells E2 and F2, on the same sheet as the data entry cells. However, I like to put the conditions in worksheet cells instead, so itвЂ™s easy to see them, and change the conditions later, if you need to. In that formula, you could hard-code the вЂњUSвЂќ and вЂњUnited StatesвЂќ conditions. WeвЂ™ll need to set up a special rule for this, using a formula. There isnвЂ™t a built-in conditional formatting rule that will do this. HereвЂ™s what the worksheet looks like, after that conditional formatting is set up in cells D5:D14

The data entry cell contains вЂњUnited StatesвЂќ.

The country code вЂњUSвЂќ is entered in cell B2.

The person who sent the question wanted to highlight cells based on 2 conditions:

#Openoffice conditional formatting based on match how to

Unless you were using the computer as excuse to hide away from all the holiday chaos! Now weвЂ™re back to work, and one of the first questions I got this year was how to highlight cells based on two conditions. Happy New Year! I hope you had time to relax over the holidays, and you stepped away from the computer for a while.