Prev: FORMULA BAR DISAPPEARED IN 2007, HOW DO I RESTORE PLEASE?
Next: using a IF statement to return a value
From: tech1NJ on 26 Mar 2010 15:11
I have a workbook with several sheets. Sheet1 has several hundred rows of
text. Some of the same text will appear in multiple rows of Sheet1. On Sheet2
I have I have a cell that allows my users to free hand type in a keyword(s)
to search for in Sheet1. This cell is also has a reference name of
"Special_Char".

Now comes the question..... I like to for the user to type the keywork into
"Special_Char" and any or all the rows in Sheet1 that contain the keyword as
part of its text to turn yellow. Is there a way to do this? Please keep in
mind that the keywork is only part of the words in each of the cell. Your
help is appreciated in advance.
--
tech1NJ
From: pbart on 26 Mar 2010 17:40
tech1NJ

I would use a column (A say) on Sheet1 to search for your user's keyword in
your text, row by row

=IFERROR(FIND(special_char,B16),FALSE)

(text here assumed to be in column B). Column B is then conditionally
formatted according to the formula in $A16 etc. A number (string found) rates
as TRUE for the test, not found gives an error that is then replaced by
FALSE. Finally you can 'hide' column A by reducing its width to zero. If
you have more keywords use extra columns to search one by one and combine the
boolean results with an =OR(A16,B16,C16) to determine the conditional
formatting to use.

I hope this helps


"tech1NJ" wrote:

> I have a workbook with several sheets. Sheet1 has several hundred rows of
> text. Some of the same text will appear in multiple rows of Sheet1. On Sheet2
> I have I have a cell that allows my users to free hand type in a keyword(s)
> to search for in Sheet1. This cell is also has a reference name of
> "Special_Char".
>
> Now comes the question..... I like to for the user to type the keywork into
> "Special_Char" and any or all the rows in Sheet1 that contain the keyword as
> part of its text to turn yellow. Is there a way to do this? Please keep in
> mind that the keywork is only part of the words in each of the cell. Your
> help is appreciated in advance.
> --
> tech1NJ
From: tech1NJ on 29 Mar 2010 12:37
This does not work....Is there any other thoughts?
--
tech1NJ


"pbart" wrote:

> tech1NJ
>
> I would use a column (A say) on Sheet1 to search for your user's keyword in
> your text, row by row
>
> =IFERROR(FIND(special_char,B16),FALSE)
>
> (text here assumed to be in column B). Column B is then conditionally
> formatted according to the formula in $A16 etc. A number (string found) rates
> as TRUE for the test, not found gives an error that is then replaced by
> FALSE. Finally you can 'hide' column A by reducing its width to zero. If
> you have more keywords use extra columns to search one by one and combine the
> boolean results with an =OR(A16,B16,C16) to determine the conditional
> formatting to use.
>
> I hope this helps
>
>
> "tech1NJ" wrote:
>
> > I have a workbook with several sheets. Sheet1 has several hundred rows of
> > text. Some of the same text will appear in multiple rows of Sheet1. On Sheet2
> > I have I have a cell that allows my users to free hand type in a keyword(s)
> > to search for in Sheet1. This cell is also has a reference name of
> > "Special_Char".
> >
> > Now comes the question..... I like to for the user to type the keywork into
> > "Special_Char" and any or all the rows in Sheet1 that contain the keyword as
> > part of its text to turn yellow. Is there a way to do this? Please keep in
> > mind that the keywork is only part of the words in each of the cell. Your
> > help is appreciated in advance.
> > --
> > tech1NJ
 | 
Pages: 1
Prev: FORMULA BAR DISAPPEARED IN 2007, HOW DO I RESTORE PLEASE?
Next: using a IF statement to return a value