Force entry to one of three cells
 

I have a sheet where users enter a value of an invoice in one of three
columns. These are Pounds sterling, US dollar and Euro. The invoice
will only ever be in one of the three currencies.

Can I flag a warning if values appear in more than one of the three
cells on each row?

Martin,

You can use Data Validation to prevent multiple entries.

Select the three columns, all rows, where the values might be entered.
Let's say columns A, B and C, starting in row 2. A2 will be the activecell.

Use Data | Validation...., select "Custom" under "Allow", and in the
"Formula" area, enter

=COUNTA($A2:$C2)=1

You can modify the message to reflect your constraints.

HTH,
Bernie
MS Excel MVP


"Martin Smith" wrote in
message ...
I have a sheet where users enter a value of an invoice in one of three
columns. These are Pounds sterling, US dollar and Euro. The invoice
will only ever be in one of the three currencies.

Can I flag a warning if values appear in more than one of the three
cells on each row?

On 2005-05-26 17:54:18 +0100, "Bernie Deitrick" <deitbe @ consumer dot
org said:

Martin,

You can use Data Validation to prevent multiple entries.

Select the three columns, all rows, where the values might be entered.
Let's say columns A, B and C, starting in row 2. A2 will be the activecell.

Use Data | Validation...., select "Custom" under "Allow", and in the
"Formula" area, enter

=COUNTA($A2:$C2)=1

You can modify the message to reflect your constraints.

HTH,
Bernie
MS Excel MVP

That works great.

Thank you very much.