Allowing only one cell in a column to have value

[gryphonrise]

I'm using Excel 2003. How do I make it so that when one cell in a selected column has a number greater than zero, all other cells in the column MUST be zero? I want to set it so that only one cell (any cell) in the column can have a value greater than zero. For example, in the column A1 through A10, I only want ONE cell to be able to contain a number value. It can be any cell in the selected column, but that one cell's value must force all the other cells to be empty.

I'm using the spreadhseet for a class schedule and I can only teach one class a day, so when a person has signed up for a particular class, the other classes cannot be taught.

I appreciate any help anyone can give me. I apologize if i'm not explaining myself very well, this is my first time to use the Excel program. Thank you all for anything you can suggest.

 

[Aaron Blood]

I'm thinking an event procedure would be able to do it.
Copy this to the code module of the appropriate sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Set watch_range = Range("A1:A10")
    If Union(watch_range, Target).Areas.Count = 1 Then
        n = Target.Value
        watch_range.Value = 0
        Target.Value = n
    End If
    Application.EnableEvents = True
End Sub

Since you're new to Excel...

You'll need to press Alt-F11 to get to the VB Editor (VBE), then in the Project Viewer double click on the sheet object you want the feature to work on. Then just copy/paste this code above to the blank code module that appears for the sheet.

 

[Gates Is Antichrist]

Welcome to the board.

This is really just a logical exercise. Do the following and report back if this logic is not obvious.

Set up an additional column to the one with the numbers - far off to the right would be fine; let's use column B.
Enter this in B1
=A1
In B2 put if (B1<>0,0,A1).
and copy down as far as appropriate in column B
(i.e., copy cell B2, mark cells B3 through B10, and paste)

Then regard column B as the the objective instead of A.

Alternatively, try this and see if it's preferable:
In B2 put if (B1<>0,"",A1)
and copy down as far as appropriate in column B

General advice: when exploring new territory in spreadsheets you
might want to make a backup copy first (or try new things on the copy without disturbing the original yet).

Feel free to ask simple and obvious questions here. It's all helpful, and it's all good.

P. S. You used the terms "empty" and "zero" somewhat interchangeably. Be aware that those are different; and that empty, null, zero, and zero-length string all have different meanings. It's not that big a deal for you now, but keep it in mind down the road.

 

[gryphonrise]

Thank you both so much for your help. The code works, but I can't figure out how to apply it several times in the same spreadsheet. A1:A10 and B1:B10 and C1:C10 and so forth all need to do the same function.

I'm afraid the logic of the second post escapes me. I'll play around with it and see if i can figure it out. I do appreciate your tips and advice, though.

Thank you both so much (and anyone else who posts!). I'm so very lost on Excel and I'm grateful you took the time to help me.

 

[Aaron Blood]

I was assuming you wanted to automatically set to zero any previous entries in column A if they existed.

 

[Gates Is Antichrist]

I just realized I made a stupid error.

Try again, with this

WRONG: In B2 put if (B1<>0,0,A1).

RIGHT: In B2 put if (B1<>0,0,A2).

and then copy B2 down.

Sorry for my carelessness.

 

[Aaron Blood]

I just realized I made a stupid error.

Try again, with this

WRONG: In B2 put if (B1<>0,0,A1).

RIGHT: In B2 put if (B1<>0,0,A2).

and then copy B2 down.

Sorry for my carelessness.

I don't think your formulas do anything to clear pre-existing entries in column A.

 

[Aaron Blood]

Thank you both so much for your help. The code works, but I can't figure out how to apply it several times in the same spreadsheet. A1:A10 and B1:B10 and C1:C10 and so forth all need to do the same function.

I'm afraid the logic of the second post escapes me. I'll play around with it and see if i can figure it out. I do appreciate your tips and advice, though.

Thank you both so much (and anyone else who posts!). I'm so very lost on Excel and I'm grateful you took the time to help me.

If you want to modify it to cover a larger range, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Set watch_range = Range("A1:D10")
    If Union(watch_range, Target).Areas.Count = 1 Then
        Set subrange = Intersect(watch_range, Target.EntireColumn)
        n = Target.Value
        subrange.Value = 0
        Target.Value = n
    End If
    Application.EnableEvents = True
End Sub

 

[Gates Is Antichrist]

I don't think your formulas do anything to clear pre-existing entries in column A.

Correct. I advocate not destroying source data, thus recommending for him to operate from column B instead of A.

BTW your solution is fine so forgive me for appearing to walk on yours. I just felt VBA was going to be too tough a bite for a newbie. (Besides, Union and Intersect make my little head hurt anyway )

 

[Aaron Blood]

Correct. I advocate not destroying source data, thus recommending for him to operate from column B instead of A.

BTW your solution is fine so forgive me for appearing to walk on yours. I just felt VBA was going to be too tough a bite for a newbie. (Besides, Union and Intersect make my little head hurt anyway )

Do you notice any difference between the code and your formulas?