Mutually Exclusive Entrees

[Skeeve1313]

I have two cells that I want to restrict the user input to the letter "x." If the user inputs an x in one cell, I would like the other cell to be blank, and vice versa. Both cells cannot contain an x at the same time, but could both be blank at the same time (initial value is blank). The cells are consecutive cells in the same column. I would prefer not to use VBA to do this, since I am relatively unfamiliar with VBA programming, but would welcome any suggestion that I can figure out how to implement.

Thanks for your help!

[Jakobshavn]

Use Data Validation > Allow - Custom > Formula

and then someting like:

=COUNTA(A1:A2)=1

[Skeeve1313]

That restricts the second x from being entered. I was hoping that I could find a way that if a user entered the 2nd x, the first one disappeared. This is for a football pool, and the user can only choose one of the two teams, if that helps. So if they choose the home team, the visiting team selection cell is blank.

[Jakobshavn]

You can do exactly that with an event macro. Are you O.K. with macros??

[Skeeve1313]

Never tried them, but am willing to try anything.

[Jakobshavn]

Install the following event macro in the worksheet code area:

Please Login or Register  to view this content.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


Note this is specific for cells A1 & A2, It can be adapted for any two cells

[Skeeve1313]

So for each pair of teams, I would need to declare individual ranges, and have separate If statements, correct?

[Jakobshavn]

Yes, but I can help.

Post the range pairs.

[Skeeve1313]

They are H5:H6, H7:H8, H9:H10, H11:H12, H13:H14, H15:H16, H17:H18, H19:H20, H21:H22, H23:H24, H25:H26, H27:H28, H29:H30, H31:H32, H33:H34, and H35:H36 for Player 1. Player two uses the same cells in Column J, Player 4 in Column L, all the way to Player 11 in Column AB.

[Jakobshavn]

Very good.....I will attack the code tomorrow.