Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Based on Question Mark (*NOT* used aswildcard)
I'm setting up a mostly text matrix (used for tracking tasks and
priorities in a larger project). What I'd like to do is set up Conditional Formatting to highlight any cell which contains a question mark, so that it will be blatantly obvious any time anyone on the team enters a question anywhere in the matrix (whether as "notes" or in a specific field to show that they don't know what to put there). The problem is that in Excel 2007's implementation of Conditional Formatting, the question mark appears to *only* be usable as a wildcard which represents any character, and I cannot find any way to tell Excel that I want to test cell contents for the actual question mark character. I've tried "?" and '?' which only results in matching for the quotation mark(s) with any character between them. Does anyone know how to accomplish this? Is it even possible in Excel 2007? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Based on Question Mark (*NOT* used as wildc
its a nice question..but i cant directly give you a formula since i dont have
my xlsoffice in this comp... I guess others may reply you with some *if*+*find*+*char* blend of functions for your conditional formulation that may highlight most of your *Question* concerns in the matrix. -- regards, driller " wrote: I'm setting up a mostly text matrix (used for tracking tasks and priorities in a larger project). What I'd like to do is set up Conditional Formatting to highlight any cell which contains a question mark, so that it will be blatantly obvious any time anyone on the team enters a question anywhere in the matrix (whether as "notes" or in a specific field to show that they don't know what to put there). The problem is that in Excel 2007's implementation of Conditional Formatting, the question mark appears to *only* be usable as a wildcard which represents any character, and I cannot find any way to tell Excel that I want to test cell contents for the actual question mark character. I've tried "?" and '?' which only results in matching for the quotation mark(s) with any character between them. Does anyone know how to accomplish this? Is it even possible in Excel 2007? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Based on Question Mark (*NOT* used as wildcard)
I don't have Excel 2007 but it should have an option to let you define
custom rules using a formula. In previous versions there was a "Formula Is" option. This will work using a "Formula Is" type of option: =COUNTIF(A1,"*~?*") -- Biff Microsoft Excel MVP wrote in message ... I'm setting up a mostly text matrix (used for tracking tasks and priorities in a larger project). What I'd like to do is set up Conditional Formatting to highlight any cell which contains a question mark, so that it will be blatantly obvious any time anyone on the team enters a question anywhere in the matrix (whether as "notes" or in a specific field to show that they don't know what to put there). The problem is that in Excel 2007's implementation of Conditional Formatting, the question mark appears to *only* be usable as a wildcard which represents any character, and I cannot find any way to tell Excel that I want to test cell contents for the actual question mark character. I've tried "?" and '?' which only results in matching for the quotation mark(s) with any character between them. Does anyone know how to accomplish this? Is it even possible in Excel 2007? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Based on Question Mark (*NOT* used aswildcard)
Unfortunately it seems that any knowledge from versions previous to
2007 isn't applicable to the new Conditional Formatting paradigm in 2007. It's *entirely* different, using a rules-based system (similar to Outlook Inbox rules wizards) and including a lot of new formatting options that didn't even exist before. While there are a lot more options to choose from, it seems to be very prescriptive in the sense of not providing the ability to create free- form formulas. There may be lots of options, but what you want had better be within that predefined list. I'd be very happy if someone proved me wrong on this and could tell me a way to accomplish what I want. Guy ---------------------------- User Experience Lead San Francisco, CA |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Based on Question Mark (*NOT* used as wildcard)
I've tried "?" and '?'
The key to getting Excel to recognize ? as a character and not a wildcard is by preceding it with the tilde character: ~?. The same is true with the * wildcard vs the * character: ~*. So, whatever you did in trying "?" and '?', try it like this ~? -- Biff Microsoft Excel MVP wrote in message ... Unfortunately it seems that any knowledge from versions previous to 2007 isn't applicable to the new Conditional Formatting paradigm in 2007. It's *entirely* different, using a rules-based system (similar to Outlook Inbox rules wizards) and including a lot of new formatting options that didn't even exist before. While there are a lot more options to choose from, it seems to be very prescriptive in the sense of not providing the ability to create free- form formulas. There may be lots of options, but what you want had better be within that predefined list. I'd be very happy if someone proved me wrong on this and could tell me a way to accomplish what I want. Guy ---------------------------- User Experience Lead San Francisco, CA |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Based on Question Mark (*NOT* used aswildcard)
On 5 déc, 04:02, "T. Valko" wrote:
I've tried "?" and '?' The key to getting Excel to recognize ? as a character and not a wildcard is by preceding it with the tilde character: ~?. The same is true with the * wildcard vs the * character: ~*. So, whatever you did in trying "?" and '?', try it like this ~? -- Biff Microsoft Excel MVP wrote in message ... Unfortunately it seems that any knowledge from versions previous to 2007 isn't applicable to the new Conditional Formatting paradigm in 2007. It's *entirely* different, using a rules-based system (similar to Outlook Inbox rules wizards) and including a lot of new formatting options that didn't even exist before. While there are a lot more options to choose from, it seems to be very prescriptive in the sense of not providing the ability to create free- form formulas. There may be lots of options, but what you want had better be within that predefined list. I'd be very happy if someone proved me wrong on this and could tell me a way to accomplish what I want. Guy ---------------------------- User Experience Lead San Francisco, CA I confirm that in 2007 as in the previous versions, adding a tilde before the ? converts ? from a wild card to a normal character. In the cf, chose "new rule" "apply cf to cells containing" then select on the left menu "specific text" (I guess this should be the label but my excel version is french, this is the second item in the list). Then type ~? It works (tested) Misange |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Based on Question Mark (*NOT* used as wildcard)
I got some help on this from MVP Niek Otten:
********* On the Home tab, choose Conditional FormattingUse a formula to determine which cells to formatFormat values where this formula is true, fill in the formula, FormatFill, choose a color The formula might be something like =NOT(ISERROR(FIND("?",A1))) Do you answer the post? -- Kind regards, Niek Otten Microsoft MVP - Excel ********** -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I've tried "?" and '?' The key to getting Excel to recognize ? as a character and not a wildcard is by preceding it with the tilde character: ~?. The same is true with the * wildcard vs the * character: ~*. So, whatever you did in trying "?" and '?', try it like this ~? -- Biff Microsoft Excel MVP wrote in message ... Unfortunately it seems that any knowledge from versions previous to 2007 isn't applicable to the new Conditional Formatting paradigm in 2007. It's *entirely* different, using a rules-based system (similar to Outlook Inbox rules wizards) and including a lot of new formatting options that didn't even exist before. While there are a lot more options to choose from, it seems to be very prescriptive in the sense of not providing the ability to create free- form formulas. There may be lots of options, but what you want had better be within that predefined list. I'd be very happy if someone proved me wrong on this and could tell me a way to accomplish what I want. Guy ---------------------------- User Experience Lead San Francisco, CA |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Based on Question Mark (*NOT* used aswildcard)
Thank you all for your responses! It turns out that the tilde
character was the trick I needed, and I didn't have to set up any special rules. I just used the cascading menus from CF within the Home tab, selected "text contains" and put "~?" into the resulting field. Hooray! Mission accomplished! Guy ---------------------------- User Experience Lead San Francisco, CA |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Based on Question Mark (*NOT* used as wildcard)
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP wrote in message ... Thank you all for your responses! It turns out that the tilde character was the trick I needed, and I didn't have to set up any special rules. I just used the cascading menus from CF within the Home tab, selected "text contains" and put "~?" into the resulting field. Hooray! Mission accomplished! Guy ---------------------------- User Experience Lead San Francisco, CA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting Based on If and Or | Excel Discussion (Misc queries) | |||
using conditional formatting to mark repeated names in list? | Excel Discussion (Misc queries) | |||
Conditional Formatting based on month name | Excel Discussion (Misc queries) |