Today we take a look at some nifty tricks in the Find dialog box. While I bet you will learn something along the way, the big problem is that we can not effectively select all of the results of Find All across multiple worksheets. So, to Norel from California who asked the question, we dont have a good answer, but for everyone else, you might find some cool tricks in Episode 657.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today, we have a question that's sent in by Norrell today.
Norrell is from California.
Thought I had a solution to his problem, but it turns out that I don't know.
Norrell Says hey, I used the FIND command a lot and I want to take the results of the FIND command and basically copy it to a spreadsheet.
He said, you know I need a list of all those places that it's found and then the thing that got me was, he said and because the results are on multiple worksheets.
Multiple worksheets...
Let's take a quick look at find.
If we choose just one field, and we go [ CTRL+F ] for Find.
Let's pull this up here.
We say we want to find all the cells that contain ABC.
Now normally, we just have this simple Find dialog box.
we click Options.
We get to see more.
And we can say that instead of just searching within this worksheet, we want to search within the whole Workbook.
And we're going to search.
Instead of formulas, we want to search in values, and we don't want to match the entire cell contents.
We want to find anything that has an ABC anywhere in it.
So, I click Find All, and it gives me this great list down here at the bottom.
And I thought well, this is Gonna be really easy.
All we have to do is choose the first item in that list, and hit [ CTRL+A ].
And, basically what that does is, it selects all of the cells that have ABC anywhere in it.
You know and then we could do whatever we need to do.
we could color those cells or you know whatever is there.
I just highlighted all the ABC cells.
But the big problem is Norrell said, Hate! My results are on multiple worksheets and sure enough this does not highlight the things on Sheet 2.
We actually have to go through the list, find the first item on sheet 2, click on it.
And then hit [ CTRL+A ] again to now select all the items on Sheet 2.
So, they're all color those yellow.
I mean I will go to Sheet 3.
Again scroll through the list and try and find Sheet 3.
[ CTRL+A ] to select all of the matches, and we can color those in some color.
And so on through the workbook.
There really is no way to affect all of the results of Find All at once when they're on multiple Workbooks.
And using the Macro recorder, there doesn't seem to be any good way or obvious way.
Let's put it that way.
Any obvious way to go through and programatically get a list of all of these items on all of the sheets that match.
So I'm putting a challenge out to you.
If you know a way to do this, basically get a list of all of these items here in the Find All results and write it to a new worksheet, that'd be great.
Just send me a note.
Bill@mrexcel.com and we'll get that on a future podcast.
Thanks to Norrell for sending in that question and thanks to you for watching.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Today, we have a question that's sent in by Norrell today.
Norrell is from California.
Thought I had a solution to his problem, but it turns out that I don't know.
Norrell Says hey, I used the FIND command a lot and I want to take the results of the FIND command and basically copy it to a spreadsheet.
He said, you know I need a list of all those places that it's found and then the thing that got me was, he said and because the results are on multiple worksheets.
Multiple worksheets...
Let's take a quick look at find.
If we choose just one field, and we go [ CTRL+F ] for Find.
Let's pull this up here.
We say we want to find all the cells that contain ABC.
Now normally, we just have this simple Find dialog box.
we click Options.
We get to see more.
And we can say that instead of just searching within this worksheet, we want to search within the whole Workbook.
And we're going to search.
Instead of formulas, we want to search in values, and we don't want to match the entire cell contents.
We want to find anything that has an ABC anywhere in it.
So, I click Find All, and it gives me this great list down here at the bottom.
And I thought well, this is Gonna be really easy.
All we have to do is choose the first item in that list, and hit [ CTRL+A ].
And, basically what that does is, it selects all of the cells that have ABC anywhere in it.
You know and then we could do whatever we need to do.
we could color those cells or you know whatever is there.
I just highlighted all the ABC cells.
But the big problem is Norrell said, Hate! My results are on multiple worksheets and sure enough this does not highlight the things on Sheet 2.
We actually have to go through the list, find the first item on sheet 2, click on it.
And then hit [ CTRL+A ] again to now select all the items on Sheet 2.
So, they're all color those yellow.
I mean I will go to Sheet 3.
Again scroll through the list and try and find Sheet 3.
[ CTRL+A ] to select all of the matches, and we can color those in some color.
And so on through the workbook.
There really is no way to affect all of the results of Find All at once when they're on multiple Workbooks.
And using the Macro recorder, there doesn't seem to be any good way or obvious way.
Let's put it that way.
Any obvious way to go through and programatically get a list of all of these items on all of the sheets that match.
So I'm putting a challenge out to you.
If you know a way to do this, basically get a list of all of these items here in the Find All results and write it to a new worksheet, that'd be great.
Just send me a note.
Bill@mrexcel.com and we'll get that on a future podcast.
Thanks to Norrell for sending in that question and thanks to you for watching.
We'll see you next time for another netcast from MrExcel.
