Force entry to one of three cells

[Martin Smith]

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?

 

[Bernie Deitrick]

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]

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.