| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Conditional Formating
I am trying to create condtional formating to turn a cell red with a complex
IF/ LOOKUP Function for the rule. The Function works outside of conditional formating but when used with in condtional formating I get no result. What do I need to do indorder for this work? The IF/LOOKUP Formula is: =IF(B5<"M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 )" " The quatation marks have nothing in them because I would like no result just the number already entered in cell "B8". -- Ben A |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Conditional Formating
Ben A wrote:
I am trying to create condtional formating to turn a cell red with a complex IF/ LOOKUP Function for the rule. The Function works outside of conditional formating but when used with in condtional formating I get no result. What do I need to do indorder for this work? The IF/LOOKUP Formula is: =IF(B5<"M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 )" " The quatation marks have nothing in them because I would like no result just the number already entered in cell "B8". The formula above would not work outside of conditional formatting. Copy it directly from a working cell to your post and try again. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Conditional Formating
=IF(B5="M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 ),"Fail"
If I enter the above formula into a cell it works great and will display the word fail within the cell. I don't want to do that though I just want the "B8" cell to change color if the statement is true. How do I do this? -- Ben A "Glenn" wrote: Ben A wrote: I am trying to create condtional formating to turn a cell red with a complex IF/ LOOKUP Function for the rule. The Function works outside of conditional formating but when used with in condtional formating I get no result. What do I need to do indorder for this work? The IF/LOOKUP Formula is: =IF(B5<"M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 )" " The quatation marks have nothing in them because I would like no result just the number already entered in cell "B8". The formula above would not work outside of conditional formatting. Copy it directly from a working cell to your post and try again. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Conditional Formating
In Conditional formatting, change to formula is from the drop-down
=IF(AND(B5="M",B8LOOKUP(B7,Data!F3:F38,Data!E3:E3 8))) Then format as needed. "Ben A" wrote: =IF(B5="M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 ),"Fail" If I enter the above formula into a cell it works great and will display the word fail within the cell. I don't want to do that though I just want the "B8" cell to change color if the statement is true. How do I do this? -- Ben A "Glenn" wrote: Ben A wrote: I am trying to create condtional formating to turn a cell red with a complex IF/ LOOKUP Function for the rule. The Function works outside of conditional formating but when used with in condtional formating I get no result. What do I need to do indorder for this work? The IF/LOOKUP Formula is: =IF(B5<"M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 )" " The quatation marks have nothing in them because I would like no result just the number already entered in cell "B8". The formula above would not work outside of conditional formatting. Copy it directly from a working cell to your post and try again. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Conditional Formating
Thank you for your help I tried your suggestion and recieved an error telling
me the formula I entered contians an error. I can't find any mistakes in my entry. Do you have any Ideas -- Ben A "Sean Timmons" wrote: In Conditional formatting, change to formula is from the drop-down =IF(AND(B5="M",B8LOOKUP(B7,Data!F3:F38,Data!E3:E3 8))) Then format as needed. "Ben A" wrote: =IF(B5="M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 ),"Fail" If I enter the above formula into a cell it works great and will display the word fail within the cell. I don't want to do that though I just want the "B8" cell to change color if the statement is true. How do I do this? -- Ben A "Glenn" wrote: Ben A wrote: I am trying to create condtional formating to turn a cell red with a complex IF/ LOOKUP Function for the rule. The Function works outside of conditional formating but when used with in condtional formating I get no result. What do I need to do indorder for this work? The IF/LOOKUP Formula is: =IF(B5<"M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 )" " The quatation marks have nothing in them because I would like no result just the number already entered in cell "B8". The formula above would not work outside of conditional formatting. Copy it directly from a working cell to your post and try again. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Conditional Formating
I think you need something like
=IF(AND(B5="M",B8LOOKUP(B7,Data!F3:F38,Data!E3:E3 8)),TRUE(),FALSE()) "Sean Timmons" wrote: In Conditional formatting, change to formula is from the drop-down =IF(AND(B5="M",B8LOOKUP(B7,Data!F3:F38,Data!E3:E3 8))) Then format as needed. "Ben A" wrote: =IF(B5="M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 ),"Fail" If I enter the above formula into a cell it works great and will display the word fail within the cell. I don't want to do that though I just want the "B8" cell to change color if the statement is true. How do I do this? -- Ben A "Glenn" wrote: Ben A wrote: I am trying to create condtional formating to turn a cell red with a complex IF/ LOOKUP Function for the rule. The Function works outside of conditional formating but when used with in condtional formating I get no result. What do I need to do indorder for this work? The IF/LOOKUP Formula is: =IF(B5<"M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 )" " The quatation marks have nothing in them because I would like no result just the number already entered in cell "B8". The formula above would not work outside of conditional formatting. Copy it directly from a working cell to your post and try again. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Conditional Formating
Thank you for every ones help but I figured it out. The solution was
=AND(IF(B5="F",B8LOOKUP(B7,Y3:Y38,X3:X38))) The reference table for the LOOKUP function had to be moved to the current worksheet and the AND needed to be moved to the begining. Again thanks for everyones help with this I don't think I could have figured it out with a little help from all of you. -- Ben A "~L" wrote: I think you need something like =IF(AND(B5="M",B8LOOKUP(B7,Data!F3:F38,Data!E3:E3 8)),TRUE(),FALSE()) "Sean Timmons" wrote: In Conditional formatting, change to formula is from the drop-down =IF(AND(B5="M",B8LOOKUP(B7,Data!F3:F38,Data!E3:E3 8))) Then format as needed. "Ben A" wrote: =IF(B5="M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 ),"Fail" If I enter the above formula into a cell it works great and will display the word fail within the cell. I don't want to do that though I just want the "B8" cell to change color if the statement is true. How do I do this? -- Ben A "Glenn" wrote: Ben A wrote: I am trying to create condtional formating to turn a cell red with a complex IF/ LOOKUP Function for the rule. The Function works outside of conditional formating but when used with in condtional formating I get no result. What do I need to do indorder for this work? The IF/LOOKUP Formula is: =IF(B5<"M",IF(B8LOOKUP(B7,Data!F3:F38,Data!E3:E38 )" " The quatation marks have nothing in them because I would like no result just the number already entered in cell "B8". The formula above would not work outside of conditional formatting. Copy it directly from a working cell to your post and try again. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex Conditional | Excel Worksheet Functions | |||
| Complex conditional formatting | Excel Worksheet Functions | |||
| Complex or Conditional Countif(s) | Excel Worksheet Functions | |||
| Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
| complex?? Q about Conditional formatting | Excel Worksheet Functions | |||
Linear Mode
