How do I restrict data entry to one of two columns but not both?
 

I have a spreadsheet with several columns that I will be asking colleagues to
fill with their data. Two of the columns are in date format and are an
either/or option. What I want to happen is that if someone inserts data into
a row in column A then they cannot insert data into the same row of column B,
or vice versa.
How would I do this?

How do I restrict data entry to one of two columns but not both?
 

Do you really want to make it IMPOSSIBLE or could you cope with this:
In the column next to the two for entries add =IF(COUNT(A2:B2)1,"ERROR","")
You could also use formatting to make this, say, red
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Gillian" wrote in message
...
I have a spreadsheet with several columns that I will be asking colleagues
to
fill with their data. Two of the columns are in date format and are an
either/or option. What I want to happen is that if someone inserts data
into
a row in column A then they cannot insert data into the same row of column
B,
or vice versa.
How would I do this?

How do I restrict data entry to one of two columns but not bot
 

Thanks, Bernard.
As the full spreadsheet has about 50 columns, I was trying to avoid adding
any more just to show messages. I shall ask the people I'm doing this for if
they'd be happy with an extra column. Otherwise, is there a way to format the
columns?
Gillian

"Bernard Liengme" wrote:

Do you really want to make it IMPOSSIBLE or could you cope with this:
In the column next to the two for entries add =IF(COUNT(A2:B2)1,"ERROR","")
You could also use formatting to make this, say, red
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Gillian" wrote in message
...
I have a spreadsheet with several columns that I will be asking colleagues
to
fill with their data. Two of the columns are in date format and are an
either/or option. What I want to happen is that if someone inserts data
into
a row in column A then they cannot insert data into the same row of column
B,
or vice versa.
How would I do this?

How do I restrict data entry to one of two columns but not bot
 

As I posted to your previous query to the same question (your previous
question had dates being entered in columns E & F).

Data Validation.
In cell E1, go to Data--Validation
Allow:
Date
Data:
Greater than or equal to
Start Date:
=IF(F10,99999,0)
Highlight column E (or at least down to where is needed), and go to
Data--Validation, and select YES for extending data validation.

Then in F1, do the same thing as in E, except the formula for start date
will be E1, highlight column F, and go to Data--Validation, YES to extend,
again, same as you did in column E.
--

--
John C


"Gillian" wrote:

Thanks, Bernard.
As the full spreadsheet has about 50 columns, I was trying to avoid adding
any more just to show messages. I shall ask the people I'm doing this for if
they'd be happy with an extra column. Otherwise, is there a way to format the
columns?
Gillian

"Bernard Liengme" wrote:

Do you really want to make it IMPOSSIBLE or could you cope with this:
In the column next to the two for entries add =IF(COUNT(A2:B2)1,"ERROR","")
You could also use formatting to make this, say, red
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Gillian" wrote in message
...
I have a spreadsheet with several columns that I will be asking colleagues
to
fill with their data. Two of the columns are in date format and are an
either/or option. What I want to happen is that if someone inserts data
into
a row in column A then they cannot insert data into the same row of column
B,
or vice versa.
How would I do this?

How do I restrict data entry to one of two columns but not bot
 

Thanks, John.
I'll have a go at this.
Sorry about the double post; I thought I had submitted the query and came
back to my computer after some time to see a pop-up asking for my email
address, but found I had been timed out, so submitted the question again. It
would appear that it had been submitted after all, by which time it had moved
down to the second page and I didn't see it.

Gillian

"John C" wrote:

As I posted to your previous query to the same question (your previous
question had dates being entered in columns E & F).

Data Validation.
In cell E1, go to Data--Validation
Allow:
Date
Data:
Greater than or equal to
Start Date:
=IF(F10,99999,0)
Highlight column E (or at least down to where is needed), and go to
Data--Validation, and select YES for extending data validation.

Then in F1, do the same thing as in E, except the formula for start date
will be E1, highlight column F, and go to Data--Validation, YES to extend,
again, same as you did in column E.
--

--
John C


"Gillian" wrote:

Thanks, Bernard.
As the full spreadsheet has about 50 columns, I was trying to avoid adding
any more just to show messages. I shall ask the people I'm doing this for if
they'd be happy with an extra column. Otherwise, is there a way to format the
columns?
Gillian

"Bernard Liengme" wrote:

Do you really want to make it IMPOSSIBLE or could you cope with this:
In the column next to the two for entries add =IF(COUNT(A2:B2)1,"ERROR","")
You could also use formatting to make this, say, red
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Gillian" wrote in message
...
I have a spreadsheet with several columns that I will be asking colleagues
to
fill with their data. Two of the columns are in date format and are an
either/or option. What I want to happen is that if someone inserts data
into
a row in column A then they cannot insert data into the same row of column
B,
or vice versa.
How would I do this?