+ Reply to Thread
Results 1 to 8 of 8

Advanced formula - Return result & Show Cell Reference of result

  1. #1
    Irv
    Guest

    Advanced formula - Return result & Show Cell Reference of result

    Hi, I'm trying to build a formula that would take the content of a cell and
    compare it to the value (yes or no), of a range of cells, then show the cell
    reference (column/row#) if the cell reference does not meet the given
    criteria. Ex....
    if... Column A, Rows 3-15 contains the letter "B"
    then... Columns "D and/or F" (rows 3-15) should contain "yes", However
    if... any of the rows contains "no" in both columns (D & F), then show which
    row..

  2. #2
    Bob Phillips
    Guest

    Re: Advanced formula - Return result & Show Cell Reference of result

    Try conditional formatting.

    Select B3:F15
    Goto CF, Format>Conditional Formatting
    Change Condition 1 to Formula Is
    Add a Formula =OR($D3="no",$F3="no")
    Click format
    Select Pattern
    Choose a colour
    OK

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "Irv" <Irv@discussions.microsoft.com> wrote in message
    news:D12CEEF8-C826-4EF2-B08D-DC6372A693B9@microsoft.com...
    > Hi, I'm trying to build a formula that would take the content of a cell

    and
    > compare it to the value (yes or no), of a range of cells, then show the

    cell
    > reference (column/row#) if the cell reference does not meet the given
    > criteria. Ex....
    > if... Column A, Rows 3-15 contains the letter "B"
    > then... Columns "D and/or F" (rows 3-15) should contain "yes", However
    > if... any of the rows contains "no" in both columns (D & F), then show

    which
    > row..




  3. #3
    Irv
    Guest

    Re: Advanced formula - Return result & Show Cell Reference of resu

    Thanks Bob, however can you please provide a bit more details.. (ex. What is
    and where do I find, CF?)

    "Bob Phillips" wrote:

    > Try conditional formatting.
    >
    > Select B3:F15
    > Goto CF, Format>Conditional Formatting
    > Change Condition 1 to Formula Is
    > Add a Formula =OR($D3="no",$F3="no")
    > Click format
    > Select Pattern
    > Choose a colour
    > OK
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove xxx from email address if mailing direct)
    >
    > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > news:D12CEEF8-C826-4EF2-B08D-DC6372A693B9@microsoft.com...
    > > Hi, I'm trying to build a formula that would take the content of a cell

    > and
    > > compare it to the value (yes or no), of a range of cells, then show the

    > cell
    > > reference (column/row#) if the cell reference does not meet the given
    > > criteria. Ex....
    > > if... Column A, Rows 3-15 contains the letter "B"
    > > then... Columns "D and/or F" (rows 3-15) should contain "yes", However
    > > if... any of the rows contains "no" in both columns (D & F), then show

    > which
    > > row..

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: Advanced formula - Return result & Show Cell Reference of resu

    CF is conditional formatting. Follow the steps I gave, it is complete.
    Format>Conditional Formatting means take the Format menu, then take
    Conditional Formatting off that menu.

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "Irv" <Irv@discussions.microsoft.com> wrote in message
    news:FD7AB3F3-C350-4326-8E92-805A3B9722ED@microsoft.com...
    > Thanks Bob, however can you please provide a bit more details.. (ex. What

    is
    > and where do I find, CF?)
    >
    > "Bob Phillips" wrote:
    >
    > > Try conditional formatting.
    > >
    > > Select B3:F15
    > > Goto CF, Format>Conditional Formatting
    > > Change Condition 1 to Formula Is
    > > Add a Formula =OR($D3="no",$F3="no")
    > > Click format
    > > Select Pattern
    > > Choose a colour
    > > OK
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (remove xxx from email address if mailing direct)
    > >
    > > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > > news:D12CEEF8-C826-4EF2-B08D-DC6372A693B9@microsoft.com...
    > > > Hi, I'm trying to build a formula that would take the content of a

    cell
    > > and
    > > > compare it to the value (yes or no), of a range of cells, then show

    the
    > > cell
    > > > reference (column/row#) if the cell reference does not meet the given
    > > > criteria. Ex....
    > > > if... Column A, Rows 3-15 contains the letter "B"
    > > > then... Columns "D and/or F" (rows 3-15) should contain "yes", However
    > > > if... any of the rows contains "no" in both columns (D & F), then show

    > > which
    > > > row..

    > >
    > >
    > >




  5. #5
    Irv
    Guest

    Re: Advanced formula - Return result & Show Cell Reference of resu

    Yes, I got that part worked out, thanks... but I still need a formula to
    compare the value of columns D&F with a value in column A, of the same row.

    For Example:
    If (column)A="B" and (column)D="no", (column)F="no", then change the color
    of the cell in column A to yellow.

    "Bob Phillips" wrote:

    > CF is conditional formatting. Follow the steps I gave, it is complete.
    > Format>Conditional Formatting means take the Format menu, then take
    > Conditional Formatting off that menu.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove xxx from email address if mailing direct)
    >
    > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > news:FD7AB3F3-C350-4326-8E92-805A3B9722ED@microsoft.com...
    > > Thanks Bob, however can you please provide a bit more details.. (ex. What

    > is
    > > and where do I find, CF?)
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Try conditional formatting.
    > > >
    > > > Select B3:F15
    > > > Goto CF, Format>Conditional Formatting
    > > > Change Condition 1 to Formula Is
    > > > Add a Formula =OR($D3="no",$F3="no")
    > > > Click format
    > > > Select Pattern
    > > > Choose a colour
    > > > OK
    > > >
    > > > --
    > > > HTH
    > > >
    > > > Bob Phillips
    > > >
    > > > (remove xxx from email address if mailing direct)
    > > >
    > > > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > > > news:D12CEEF8-C826-4EF2-B08D-DC6372A693B9@microsoft.com...
    > > > > Hi, I'm trying to build a formula that would take the content of a

    > cell
    > > > and
    > > > > compare it to the value (yes or no), of a range of cells, then show

    > the
    > > > cell
    > > > > reference (column/row#) if the cell reference does not meet the given
    > > > > criteria. Ex....
    > > > > if... Column A, Rows 3-15 contains the letter "B"
    > > > > then... Columns "D and/or F" (rows 3-15) should contain "yes", However
    > > > > if... any of the rows contains "no" in both columns (D & F), then show
    > > > which
    > > > > row..
    > > >
    > > >
    > > >

    >
    >
    >


  6. #6
    Bob Phillips
    Guest

    Re: Advanced formula - Return result & Show Cell Reference of resu

    =AND($A3="B",OR($D3="no",$F3="no"))


    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "Irv" <Irv@discussions.microsoft.com> wrote in message
    news:20F2407C-7549-4D24-B8AB-0ECBD087F8A9@microsoft.com...
    > Yes, I got that part worked out, thanks... but I still need a formula to
    > compare the value of columns D&F with a value in column A, of the same

    row.
    >
    > For Example:
    > If (column)A="B" and (column)D="no", (column)F="no", then change the color
    > of the cell in column A to yellow.
    >
    > "Bob Phillips" wrote:
    >
    > > CF is conditional formatting. Follow the steps I gave, it is complete.
    > > Format>Conditional Formatting means take the Format menu, then take
    > > Conditional Formatting off that menu.
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (remove xxx from email address if mailing direct)
    > >
    > > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > > news:FD7AB3F3-C350-4326-8E92-805A3B9722ED@microsoft.com...
    > > > Thanks Bob, however can you please provide a bit more details.. (ex.

    What
    > > is
    > > > and where do I find, CF?)
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > Try conditional formatting.
    > > > >
    > > > > Select B3:F15
    > > > > Goto CF, Format>Conditional Formatting
    > > > > Change Condition 1 to Formula Is
    > > > > Add a Formula =OR($D3="no",$F3="no")
    > > > > Click format
    > > > > Select Pattern
    > > > > Choose a colour
    > > > > OK
    > > > >
    > > > > --
    > > > > HTH
    > > > >
    > > > > Bob Phillips
    > > > >
    > > > > (remove xxx from email address if mailing direct)
    > > > >
    > > > > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > > > > news:D12CEEF8-C826-4EF2-B08D-DC6372A693B9@microsoft.com...
    > > > > > Hi, I'm trying to build a formula that would take the content of a

    > > cell
    > > > > and
    > > > > > compare it to the value (yes or no), of a range of cells, then

    show
    > > the
    > > > > cell
    > > > > > reference (column/row#) if the cell reference does not meet the

    given
    > > > > > criteria. Ex....
    > > > > > if... Column A, Rows 3-15 contains the letter "B"
    > > > > > then... Columns "D and/or F" (rows 3-15) should contain "yes",

    However
    > > > > > if... any of the rows contains "no" in both columns (D & F), then

    show
    > > > > which
    > > > > > row..
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  7. #7
    Irv
    Guest

    Re: Advanced formula - Return result & Show Cell Reference of resu

    Thanx Bob, you really know your stuff... I am now able to combine your other
    suggestions with this formula to accomplish what I want to.. In fact I even
    got more. Truely greatful

    Irv


    "Bob Phillips" wrote:

    > =AND($A3="B",OR($D3="no",$F3="no"))
    >
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove xxx from email address if mailing direct)
    >
    > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > news:20F2407C-7549-4D24-B8AB-0ECBD087F8A9@microsoft.com...
    > > Yes, I got that part worked out, thanks... but I still need a formula to
    > > compare the value of columns D&F with a value in column A, of the same

    > row.
    > >
    > > For Example:
    > > If (column)A="B" and (column)D="no", (column)F="no", then change the color
    > > of the cell in column A to yellow.
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > CF is conditional formatting. Follow the steps I gave, it is complete.
    > > > Format>Conditional Formatting means take the Format menu, then take
    > > > Conditional Formatting off that menu.
    > > >
    > > > --
    > > > HTH
    > > >
    > > > Bob Phillips
    > > >
    > > > (remove xxx from email address if mailing direct)
    > > >
    > > > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > > > news:FD7AB3F3-C350-4326-8E92-805A3B9722ED@microsoft.com...
    > > > > Thanks Bob, however can you please provide a bit more details.. (ex.

    > What
    > > > is
    > > > > and where do I find, CF?)
    > > > >
    > > > > "Bob Phillips" wrote:
    > > > >
    > > > > > Try conditional formatting.
    > > > > >
    > > > > > Select B3:F15
    > > > > > Goto CF, Format>Conditional Formatting
    > > > > > Change Condition 1 to Formula Is
    > > > > > Add a Formula =OR($D3="no",$F3="no")
    > > > > > Click format
    > > > > > Select Pattern
    > > > > > Choose a colour
    > > > > > OK
    > > > > >
    > > > > > --
    > > > > > HTH
    > > > > >
    > > > > > Bob Phillips
    > > > > >
    > > > > > (remove xxx from email address if mailing direct)
    > > > > >
    > > > > > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > > > > > news:D12CEEF8-C826-4EF2-B08D-DC6372A693B9@microsoft.com...
    > > > > > > Hi, I'm trying to build a formula that would take the content of a
    > > > cell
    > > > > > and
    > > > > > > compare it to the value (yes or no), of a range of cells, then

    > show
    > > > the
    > > > > > cell
    > > > > > > reference (column/row#) if the cell reference does not meet the

    > given
    > > > > > > criteria. Ex....
    > > > > > > if... Column A, Rows 3-15 contains the letter "B"
    > > > > > > then... Columns "D and/or F" (rows 3-15) should contain "yes",

    > However
    > > > > > > if... any of the rows contains "no" in both columns (D & F), then

    > show
    > > > > > which
    > > > > > > row..
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >


  8. #8
    Irv
    Guest

    Re: Advanced formula - Return result & Show Cell Reference of resu

    Woooweee! Bob, I finally figured it out. Your assistance certainly pointed
    me in the right direction. This is what I ended up with and it worked.

    I highlighted the rows, by clicking the row numbers of all the rows I wanted
    to query. Then selected Format / Conditional Format
    condition 1: =IF(--($F18="B")--($F18="D"),AND($M18="no",$O18="no"))
    [format: color=yellow]
    condition 2: =IF($F18="c",AND($L18="no",$N18="no")) [Format color=purple]
    condition 3: =IF($F18="O",AND($L18="no",$N18="no")) [Format color=green]

    I would still appreciate any comments and/or suggestions.. Also, is there a
    way to use the same formula without first going into "conditional
    formatting"? and still be able to show the results with the different colors
    schemes?


    "Bob Phillips" wrote:

    > =AND($A3="B",OR($D3="no",$F3="no"))
    >
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove xxx from email address if mailing direct)
    >
    > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > news:20F2407C-7549-4D24-B8AB-0ECBD087F8A9@microsoft.com...
    > > Yes, I got that part worked out, thanks... but I still need a formula to
    > > compare the value of columns D&F with a value in column A, of the same

    > row.
    > >
    > > For Example:
    > > If (column)A="B" and (column)D="no", (column)F="no", then change the color
    > > of the cell in column A to yellow.
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > CF is conditional formatting. Follow the steps I gave, it is complete.
    > > > Format>Conditional Formatting means take the Format menu, then take
    > > > Conditional Formatting off that menu.
    > > >
    > > > --
    > > > HTH
    > > >
    > > > Bob Phillips
    > > >
    > > > (remove xxx from email address if mailing direct)
    > > >
    > > > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > > > news:FD7AB3F3-C350-4326-8E92-805A3B9722ED@microsoft.com...
    > > > > Thanks Bob, however can you please provide a bit more details.. (ex.

    > What
    > > > is
    > > > > and where do I find, CF?)
    > > > >
    > > > > "Bob Phillips" wrote:
    > > > >
    > > > > > Try conditional formatting.
    > > > > >
    > > > > > Select B3:F15
    > > > > > Goto CF, Format>Conditional Formatting
    > > > > > Change Condition 1 to Formula Is
    > > > > > Add a Formula =OR($D3="no",$F3="no")
    > > > > > Click format
    > > > > > Select Pattern
    > > > > > Choose a colour
    > > > > > OK
    > > > > >
    > > > > > --
    > > > > > HTH
    > > > > >
    > > > > > Bob Phillips
    > > > > >
    > > > > > (remove xxx from email address if mailing direct)
    > > > > >
    > > > > > "Irv" <Irv@discussions.microsoft.com> wrote in message
    > > > > > news:D12CEEF8-C826-4EF2-B08D-DC6372A693B9@microsoft.com...
    > > > > > > Hi, I'm trying to build a formula that would take the content of a
    > > > cell
    > > > > > and
    > > > > > > compare it to the value (yes or no), of a range of cells, then

    > show
    > > > the
    > > > > > cell
    > > > > > > reference (column/row#) if the cell reference does not meet the

    > given
    > > > > > > criteria. Ex....
    > > > > > > if... Column A, Rows 3-15 contains the letter "B"
    > > > > > > then... Columns "D and/or F" (rows 3-15) should contain "yes",

    > However
    > > > > > > if... any of the rows contains "no" in both columns (D & F), then

    > show
    > > > > > which
    > > > > > > row..
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1