At 10:35 07/04/2010 -0400, Andy Graybeal wrote:
We use Calc 3.2.0 to schedule our employees and we're looking for a
way to alert us if we enter the same name twice in any column
(columns are days). The idea is to not accidentally schedule
someone twice per day. We do occasionally schedule people for two
shifts in one day, so we don't want it to prohibit us, just alert us
maybe with font color change? Here is the sample file we're using:
http://casanueva.com/sample1.ods
First, create a suitable cell style:
o Go to Format | Styles and Formatting (or click the Styles and
Formatting button in the Formatting toolbar, or press F11).
o Click the Cell Styles button.
o With the Default style highlighted, click the "New Style from
Selection" button.
o Give your new style a name - perhaps "Alert".
o Right-click the new style name in the list and select Modify... .
o Adjust the style as required: e.g. go to the Font Effects tab and
set the "Font color" to Light red.
Apply the style conditionally:
o Select a sample cell (e.g. B2 in your sample document).
o Go to Format | Conditional Formatting... .
o With "Condition 1" ticked, select "Formula is" from the first
drop-down menu.
o In the box, enter the formula: COUNTIF(B$2:B$60;B2)>1
o For Cell Style, select your new cell style - and click OK.
Copy and paste the conditional formatting:
o With the sample cell selected, click the Format Paintbrush in the
Standard toolbar (once only).
o Drag the paint-bucket cursor over the relevant cells (B2 to H60 in
your sample).
How does it work? The COUNTIF formula counts how many cells in the
relevant column are the same as the cell in question - including that
cell itself. (The columns and rows are adjusted as necessary, but
the row range remains fixed.) If there are more than one such cell,
then you have duplicates and the alerting cell style is applied.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
