| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced formula - Return result & Show Cell Reference of result
Try conditional formatting.
Select B3:F15 Goto CF, FormatConditional 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" wrote in message ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, FormatConditional 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" wrote in message ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Advanced formula - Return result & Show Cell Reference of resu
CF is conditional formatting. Follow the steps I gave, it is complete.
FormatConditional 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" wrote in message ... 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, FormatConditional 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" wrote in message ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. FormatConditional 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" wrote in message ... 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, FormatConditional 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" wrote in message ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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" wrote in message ... 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. FormatConditional 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" wrote in message ... 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, FormatConditional 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" wrote in message ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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" wrote in message ... 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. FormatConditional 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" wrote in message ... 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, FormatConditional 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" wrote in message ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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" wrote in message ... 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. FormatConditional 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" wrote in message ... 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, FormatConditional 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" wrote in message ... 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| My formulas return the formula not a result in the cell | Excel Worksheet Functions | |||
| Formula Showing In A Cell Instead of Proper Result | New Users to Excel | |||
| copied formula has correct cell reference, but result of original | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| formula to return the value of a cell based on a looked up true reference | Excel Worksheet Functions | |||
Linear Mode
