Selecting "Territory" in a column

  • Thread starter Thread starter William Jewell
  • Start date Start date
W

William Jewell

Hi,

I have a workbook with multiple sheets that have interlinking formulas.
Several sheets adjust information based on entering an "x" in a cell in
"Territory" column. I have two questions that I hope someone can help
me with:

1) Is there a way of automatically preventing two entries in the range
at the same time? In other words, when I enter "x" in one cell, is
there some way to empty any other cells in the column range? Currently
two or more cells can have the required "x" but only the first cell
controls all the calculations. I have a warning message whenever
someone clicks on any of the entry cells but would like to handle this
automatically.
2) Is there a relatively easy way to simply click on the desired cell,
clear all cells in the column range and insert an "x" or a check and
entering as a result?

I'm pretty good at making excel give me what I need but have no VB
experience.

Any guidance on this would be very helpful.

Thank you!
 
Hi
1) Is there a way of automatically preventing two entries in the range
at the same time? In other words, when I enter "x" in one cell, is
there some way to empty any other cells in the column range? Currently
two or more cells can have the required "x" but only the first cell
controls all the calculations. I have a warning message whenever
someone clicks on any of the entry cells but would like to handle this
automatically.
Click to expand...

I would use data validation to prevent two (or more) entries. Highlight
the entire row, Goto 'Data - Validation' and enter the following
formula (under 'custom'):
=AND(COUNTIF($A$1:$A$99,A1)<=1,A1="x")
will allow only one 'x' in column A
Note: IMHO it is not possible to automatically delete other cells based
on an entry in a different cell. One possible solution to achieve this
would be to process the worksheet_change event but I think data
validation works just fine


HTH
Frank
 
Back
Top