Does a Formula exist?

[quicklearner]

I have used excel to create a legal document matrix. The columns are labeled to separate and identify dates, attorneys, abbreviated motion language and complete motion language. The rows will be identified with a tab number to correspond to how/where the document is filed.

Due to the number of documents in this matrix, I would like to insert a formula(?)that would enable a document (or documents) to be located based upon a word search. Is this possible to do with excel? I am not opposed to inserting programming language, the answer would just need to tell me how and where since I have no experience in Visual Basic.

Thank you.

 

[KenWright]

Got me confused - Give us an example of the data.

Where in the workbook is the word you will be searching for - Are they all on a single Index sheet? Are the words in single cells on their own, or are they part of a string inside a cell? When the word is found, what is it that is associated with that word that you want returned, ie is it a document number in another cell on the same row, or is it a tab number (by which I assume you mean a Worksheet number).

Regards
Ken....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[telephoto]

This sounds possible for Excel, but heavy.

Use Access. if you have the spreadsheet data already then import it into a new Access table.

Make a query to look for the text.

Make a form to display the results of the query.

No VBA required.


Telephoto

 

[SkipVought]

telephoto,

Don't have to import into Access to be able to query an Excel Table -- can do a Data/Get External Data - MS Query directly in Excel as long as the Excel table is NAMED as a table in a single sheet.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Zathras]

quicklearner, have you tried using AutoFilter? you can set a custom filter to "Show rows where" the cell "contains" specific text for which you are looking. Depending on how much data you have, it may serve your purpose.

 

[quicklearner]

Okay guys I am going to review your answers and determine which one fits the bill for my needs. I need to keep costs down for my client, so naturally the easiest course will be my choice. I'll let you know in a few days what works.:->

To answer Ken Wright's question:
1. "Are they all on a single index sheet?"
If you mean a worksheet, the answer is yes.
2. "Words in single cells, or part of a string inside a cell?"
The word search I would conduct is part of a string inside a cell.
3. "When the word is found, what is it that is associated with that word that you want returned?
The document tab number in the row. In some cases, the search would bring up multiple rows. The worksheet tab is titled with the case number. (It mimics the title in the header)

The columns are identifed by "Tab#", "Dated Filed", "Date of Document", "Attorney", "Motion", "Complete Motion Language." There are no row markers. I could have made this up in Word and made a chart, but I needed search capability and thought excel would work.

 

[KenWright]

From what you say I think Zathras has the way to go. Should take a few minutes to set up unless I have this all wrong. You can take either an Autofilter or Advanced Filter route, but I think Autofilter is simpler for some.

With all your data as stated, and assuming that whatever column is going to contain your strings with the word you are looking for, (I'll assume Col A starting A5), then in A1 have the text "Search for:-" and in A2 put whatever text you will search for eg abc.

Now in any empty column (say Col K), in K5 put the following formula:-

=NOT(ISERROR(FIND($A$2,A5)))

and copy down as far as your data goes.

Now in A3 put the heading "SHOW" Y/N", then select A4 down as far as the data goes and do Data / Filter / Autofilter

A dropdown arrow should appear in cell A4. Click on it and select TRUE and you will see only therecords that match your criteria.

Tidying up - Shrink Col K and format the data as white - Hides it from show and won't affect it.

For intros to both Autofilter and Advanced Filter take a look here:-

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

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

Have got a dummy workbook with this example in if you like.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[quicklearner]

Ken,
I do believe Zathras is the closest to my desired goal. I had used Autofilter a year ago on a different project but somehow forgot how to set it up.

I did try the MS Query, which I think would also work, but I'm having trouble with setting it up. I'm being told that "this datasource contains no visible tables." So this will take alittle more doing. I am also having trouble with my ActiveX control with the excel help section. Its not my browser, so I think my ouactrl.ocx file is bad.

Regarding using Access. I could use this program, but can't take the time to set it up. Just how much time would it take to set up my excel data in Access as well as set up queries for document/word searches?

All of you have been very helpful and informative. Feel free to post any other questions or answers/solutions to this thread. I like being reminded and challenged.

 

[telephoto]

Just logged on after a delay.

Quicklearner, Can your customer use Ctrl+F? Then he just types the key word/exact phrase, looks for any part of the cell and gets moved to it.

I, personally, still favour Access. Time to do it, 10min? Tidy up and make a nice form 30min?

If you want the details:

In Access, tables screen, menu file get external data, import, change to excel file type, import the data. This will transfer the data to Access. If you want to keep the Excel file as your main source then use "link" instead of import.

You now have a table in the frame. Switch to query screen.
New query, design view, select the table. Double click on the star. Star appears below.
Double click on the field you want to search, That field appears, click on the display box to "uncheck" it as you don't want to see it twice.
In search criteria type
Like "*" & [Enter search word] & "*"
Save query.

Move to Form screen and make a new form, use the wizard to base it on the query. Tidy up the default form layout, enlarge any boxes that are too small etc.

Go to menu Tools, startup options, and set the form to start automatically.

You can tidy up with other forms/buttons etc, but those are the basic steps.


SkipVought Thanks for comment on Query - but I prefer having a form layout in Access to seeing the result in a spreadsheet. That's my choice but wouldn't it be boring if everyone did/wanted exactly the same thing the same way?


HTH

Your choice quicklearner....

Telephoto

 

[BenRowe]

I had luck Selecting a Column, Data - AutoFilter and then pressing down arrow and clicking on (Custom). You can include an * before and after any word(s) you enter in case the item you are searching for has words before and after.

By the way, the No visible table error message is because you need to give a name (define) to the area of the worksheet that is functioning as your database. You would use Insert - Name - Define. Don't use a common Database term as your name.

 

[ChasM01]

First make certain that each and every column has a title above it.

Next Highlight the entire range from the titles through the lowest/last row.

Then Select Data - Form in the menu; it will automatically produce a database type interface using all the titles you supplied. Use the criteria button to get a blank record where you can enter the phrase you are searching for.