(lively music)
- [Instructor] So here's our table of data.
We have obviously four different columns.
We're going to want to search the Vendor column.
So what we're going to imagine
is that we're auditing this list of transactions.
And you wanna look for certain last names
but they might be in the text somewhere.
So let's come down to something
that we'll be looking for.
We want to see this last name.
K-I-E-H-N.
You have some suspicions about transactions
with this person
but they're buried in the text,
so he exists on line 10
but this last name is also on line 17.
And we're going to end up just highlighting those
and not hiding anything.
So let's come over into cell E2.
And what we're going to be using,
believe it or not,
is a function called SEARCH.
So let's type that in.
And this is going to return the position
of the characters that you type in the text.
So if it's the fifth letter,
it's going to return a five.
If it doesn't found it though,
it's not going to return anything,
so we're going to keep that in mind
as we work around this.
If you want it case sensitive,
you have to use the function called FIND.
So just remember that, just swap that out
and do everything else the same.
That should be fine.
But when you're doing SEARCH,
if I type the parentheses,
it's going to give me the text
and it's giving me a hint,
it says what do you want me to search for?
So in this case, let's just type in a string
that we know is in there.
So I'll start with a quote.
And I'll type in K-U-L-A-S.
I see that in C3 right now.
K, and I won't do the case right just to show you.
All right, close that off,
do a comma and then it's asking the text to search.
So it's asking for a range of where to look.
And we'll give it C2
because that's where we wanna search.
We can only do this one at a time
before we put it in conditional formatting.
And we'll hit enter and it just returns VALUE.
It's a VALUE error because it says I can't find anything.
But let's drag this down
into all of these other cells
and what you'll notice is it returned a six
for row three because that's the only row
that has the K-U-L-A-S.
So that's just a way to show you
how the SEARCh function works.
It returns an error if it doesn't find what you type
and it returns a value if you do type something
that you're looking for.
So let's skip out of this.
That was just a little proof of concept
and we're going to build
a conditional formatting formula for it.
In the conditional formatting,
we're going to want to make an input box
where you can type
and the conditional formatting row
will look inside that box.
So let's just label this
Search Box.
Not very original.
Where you type it in,
we'll give it a little bit of highlighting
just to indicate that it's an input field.
And we'll leave this as it is for now and we'll come over
and build our conditional formatting rule.
So to make it easier,
let's just highlight each entire column.
We're going to want to format the entire row,
not just the cell in column C
just to make it look nicer,
so we'll go A, B, C, D
and if you grab the columns like this
from the top, it'll grab the entire column,
so all the way down to the bottom.
And you would go to Format, Conditional Formatting.
I happen to have already clicked that,
so if you look on the right-hand side,
the window is up here for me.
Conditional formatting rules.
And I'll left click on add another rule.
All right, so the apply to range is filled out.
A1 to D1003.
I really don't need the header.
Let's just change that
just for this a little bit more proper.
And what we want to do,
we wanna format the cells of the SEARCH function,
return something greater than a zero, right?
And that's not a built-in function,
so you do have text contains,
so you can use that if you want.
And you won't need the search box.
But then you won't have a search box,
so you'll have to go back into this formula every time
if you wanna change the words.
So that's just for a different application.
So let's come down to custom formula is.
And that just gives us an input box.
It's waiting for a formula,
so we're going to use that SEARCH function
that we just tested out a minute ago
but we're going to customize it a little bit.
So we will hit Enter
to let Sheets know that we're gonna type in a formula.
The box is red right now
'cause the formula's not done.
Okay, so we're telling it we're using the SEARCH function.
All right, so the SEARCH function, the first value
that it wants is what to search for
and every time, this is a trick,
you have to get these dollar signs in here
because we need to use a fixed reference.
Every time, no matter where it is,
even though right now it's in A2,
we want it to look at F3
no matter what.
So we put the dollar signs before the F
and before the three because we wanted
to fix the column and row reference.
The next value that it wants is where to search.
So each row, we only want it to search column C
in that row.
So for A2, we want it to highlight,
if this works, when it looks at C2.
So we're going to fix the C
and then type a two, we don't fix the two
because we want that to be able
to shift down on the next row.
So let's close that off.
Our SEARCH function is done.
But we want this highlighted
if the SEARCH function returns a number, right?
So we'll just say greater than zero.
And it's giving you a false return right now
because nothing's in the search box.
So it's actually finding the string and nothing.
We'll hit Done.
And let's come over to this search box
and we'll give it a try.
So we'll type A-B-B-O-T, hit Enter.
And there you go.
So it found Abbot.
So we'll act like you're an auditor
and you wanted to use this
because you wanted to highlight,
let's say, one of the owners
of the corporation's last name is K-I-E-H-N
and you think that they might have set up
some fake vendors in here.
So let's go ahead and type K-I-E-H-N
and hit Enter, and it highlights three transactions
but they're all different
but they have that string somewhere in their name.
So it's still showing the rest of the list
but then you can use this to focus in on a couple
of the transactions that you wanna look at.
All right, so now let's apply
your conditional formatting knowledge
to building an interactive check list.
So every task that you check off,
it's going to cross it off
and highlight the entire row in a gray
to make it look like it's gone away.
So I'll see you in that video. Thanks.