Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditions (excel 2003)

    Hello all,

    I would like to do something...not sure how to it...here it goes.

    I have to schedule multiple stores on one sheet, so say for simplicity I am using cell A1 and A2. When A1 has the value 12-9, i want a2 to say "working at a1".
    Now, if A2 in filled in first instead, then a1 would say "working at a2". This can be done initially if all the formulas are set up first, but as soon as the user writes something in a1, they would overwrite the formula and then if they decide to change it, it would not work anymore. I tried to use conditional formatting, using the FORMULA IS condition I can at least black out either cell if the other is filled.

    I do this...

    In cell A1....I use FORMULA IS =a2<>""...then cell color set to red
    in cell a2...i use FORMULA IS = a1<>""...then cell color set to red

    the problem is I can't seem to get text in...i really want text so people see where they work if they look at a schedule from another store.

    Any help would be appreciated

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditions (excel 2003)

    By nature it is not possible for a cell to contain 2 different values at the same time. I dare to ask if it would not be sufficient to store the "Working at" phrase in B1 or B2.
    I'm sure a little macro can be developed to over-ride the value and place a msg into the cell that is blank.
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditions (excel 2003)

    Are the entries by the user always of a specific type, e.g. a date, or do they follow a certain pattern? If so, you could use the Worksheet_Change event. But what if the user enters (or tries to enter) a "valid" entry in both A1 and A2? Each entry would undo the other...

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditions (excel 2003)

    I can put the "working at" in any cell sure, that won't be a problem!!! I was really hoping to not use code but if I have to I will.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditions (excel 2003)

    The cells could really have anything put in them. As for the two valid entries....it would be ok if one overwrote the other, that would actually prevent schedualing of the same person at two different locations at the same time.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditions (excel 2003)

    You could add code like this to the worksheet object:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("A1")) Is Nothing Then _
    Range("a2") = "working at A1"
    If Not Intersect(Target, Range("A2")) Is Nothing Then _
    Range("a1") = "working at A2"
    Application.EnableEvents = True
    End Sub</pre>


    If A1 is changed, A2 is replaced with "Working at A1" and if A2 is changed, A1 will be replaced with "Working at A2". Modify as needed

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •