Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Format based on criteria (2003)

    Loungers

    I hope someone can help with this - I'm trying to work out a way to format cells in one worksheet based on criteria contained in another Tab in the worksheet.

    The attached sample should help to clarify this.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Format based on criteria (2003)

    Hi Dean,

    Here's one way:
    . name cell D5 on the Planner Worksheet 'Source'
    . make your conditional format formula =OFFSET(Source,ROW()-4,COLUMN()-3)="x"
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Format based on criteria (2003)

    macropod - thanks for the reply - will give it a go

    Much appreciated

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Format based on criteria (2003)

    Macropod,

    I don't think that what you suggested is exactly what I need. I'll try to clarify.
    I want to be able to enter a position name in column 3 in the Matrix tab, ie operator, when the position name is entered the cells would fill (based on a conditional color format) where the position name and the x match from the planner tab

    I hope that makes more sence

    Regards

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Format based on criteria (2003)

    Macropod, (i replied to myself)

    I don't think that what you suggested is exactly what I need. I'll try to clarify.
    I want to be able to enter a position name in column 3 in the Matrix tab, ie operator, when the position name is entered the cells would fill (based on a conditional color format) where the position name and the x match from the planner tab

    I hope that makes more sence

    Regards

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Format based on criteria (2003)

    Hi Dean,

    In that case, name D512 'Position' (you've already got D612 named that way), name D5:J5 'Course', and try:
    =OFFSET(Course:Position,MATCH($C5,Position,0)-1,MATCH(D$4,Course,0)-1)="x"
    as your Conditional Formatting formula.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Format based on criteria (2003)

    Hi macropod,

    That works just fine - thanks very much

    Regards

  8. #8
    New Lounger
    Join Date
    Dec 2003
    Location
    Clinton Twp., Michigan, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format based on criteria (2003)

    I would like to be able to use this formula but I am not able to get it to work. Can you provide a step by step list of entries please. I understand the concept just can't the darn thing to work.

    thanks.

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Format based on criteria (2003)

    Hi,

    If you:
    . edit Dean's workbook as suggested in my last reply to him (and remove the existing shading)
    . paste the formula into the Conditional Formating dialogue box
    . choose an appropriate shading or whatever,
    it should work for you just as it did for Dean.

    If you're trying to use the formula in something else, it will need to be adjusted to suit, but you haven't given any details.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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