Formulae, syntax, codes... my head is swimming

[Laura McG.]

I don't want to bombard anyone with a complex problem and I can handle
a response like "we're not scientists, lady...". But, I'll try my
chances...

I have a very simple worksheet consisting of 2 columns. The first
column (A) contains a book volume number and the second column (B)
contains a description of what's in that particular book volume. What
I essentially want to do is to be able to perform a search of a
keyword in column B (ie. Bermuda or British Isles, etc.) and have the
book volume number/s appear, or be highlighted. Performing a search
with the FIND function works okay but if multiple book volumes are
found containing the specific keywords I would have ot TAB through
each one and write the book volume numbers down.

Is there a way that I can highlight specific Rows of data
simultaneously when performing specific searches? Or is there a
function or formula that will pop a window open for me with the list
of book volumes containing the specified words?

I'm not sure of the best way to get what I want, or even what is
available to me in terms of possibilities. I am sure they are
practically endless - I just have a major handicap when it comes to
Excel.

Please help, if you can. I would be more than happy to trade my
Design & Photography knowledge for a few tips in Excel.

Thank you so much!

L

 

[JE McGimpsey]

Is there a way that I can highlight specific Rows of data
simultaneously when performing specific searches? Or is there a
function or formula that will pop a window open for me with the list
of book volumes containing the specified words?

The first thing I would recommend is an Autofilter. Make sure that row 1
contains headings (e.g., Book ID, Subject, etc.), it makes Autofilter
much cleaner, and is almost required if you need to do an Advanced
Filter.

Select a cell in your range, choose Data/Filter/AutoFilter. Click on the
filter dropdown control at the top of column B and choose "Custom
Filter". In the top dropdown, choose "contains", then enter "Bermuda"
(without quotes) in the textbox. Click OK and all the rows that *don't*
contain Bermuda in column B will be hidden.

If you need to copy the remaining rows, Select the range to copy, then
choose Edit/Go To/Special... and select the "Visible cells only" radio
button. You can then copy and paste just the visible cells.

That may be all you need, but if you want more filtering capability,
including extracting data to different worksheets, check out
Data/Filter/Advanced filter. You can get a wealth of information on
Advanced Filter from Help, but it's better organized at Debra
Dalgleish's site:

http://www.contextures.com/xladvfilter01.html

For both solutions, you can automate them with macros - try recording a
macro (Tools/Macro/Record New Macro). Post back if you need help
changing the macro to fit your needs.