Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Winchester, Hampshire, United Kingdom
    Posts
    64
    Thanks
    1
    Thanked 1 Time in 1 Post

    Nesting Conditional formulae (XL 2003 SP2)

    There may be something on this already, but I'm trying to make a field fill conditionally in all subsidiary sheets if (a) there is an entry in a summary sheet cell, but not if ([img]/forums/images/smilies/cool.gif[/img] that entry relates to a specific named subsidiary sheet, in which case it should only show on that subsidiary sheet and not on any others.

    I have attached a sample workbook with 3 sheets, the summary and 2 subsidiary sheets (customer sheets). The workbook contains client (customer) records (subsidiary sheets), each for a specific customer, with a starting balance and incremented by money paid out on their behalf (disbursements) and interest to be charged. Interest is subtotalled every quarter so it can be notified to the customer. The only other event is when that customer's case concludes and it is closed. The "closed" event should only show on that specific customer sheet and not on any others, which continue as normal, and all further entries on the closed sheet will cease. Because there are several conditions I am struggling to get the formula to work properly. Data entry takes place on the summary sheet only, when an event takes place, and is entered manually.

    I hope that makes sense and any help will be appreciated

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

    Re: Nesting Conditional formulae (XL 2003 SP2)

    Will entries on the summary sheet ever be edited after they have first been entered?

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    Winchester, Hampshire, United Kingdom
    Posts
    64
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Nesting Conditional formulae (XL 2003 SP2)

    Hi Hans

    Not if that makes the set up easier. Alternatively the entries on the summary could be selected from a drop-down to prevent alteration.

    thanks

    richard

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

    Re: Nesting Conditional formulae (XL 2003 SP2)

    If noone else posts a reply, I'll look into this later today.

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    Winchester, Hampshire, United Kingdom
    Posts
    64
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Nesting Conditional formulae (XL 2003 SP2)

    Thanks Hans, I really appreciate it

    regards

    richard

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

    Re: Nesting Conditional formulae (XL 2003 SP2)

    Doing this with formulas becomes rather complicated. I have created a macro in the attached version that will update the sheets. Modifications applied to the sheets will be overwritten. Test thoroughly on a copy of your real workbook!

  7. #7
    Star Lounger
    Join Date
    Apr 2001
    Location
    Winchester, Hampshire, United Kingdom
    Posts
    64
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Nesting Conditional formulae (XL 2003 SP2)

    Thanks, Hans, I will have a go with this today

    much appreciated

    richard

Posting Permissions

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