Excel use as a searchable database on multiple criteria

I am wondering if someone can point me in the right direction when it comes to using an excel sheet (which is already built and rather large) for functions which are more suited to database functions.

The excel contains audiotaped medical info in columns:
question
physician name
diagnosis/subject
timecode
label1 (checked box "on")
label2 (checked box "off")
comments

each of these are sectioned by audiotape with headers --
looking something like this --
TAPE 1
diagnosis/subject1
then under that, columns
question | physician name | timecode | label1 | label2

then
diagnosis/subject2
then under that columns
subject/question | physician name | timecode | label1 | label2

then
TAPE 2
with similar info
so each TAPE has numerous DIAGNOSES covered under which there are various questions asked and answered by individual doctors.
(see link to dummy file for visual)
http://markwilkinson.cihost.com/barr...edfile.xls.zip

I need to be able to search on a group of criteria simultaneously:
# physician’s name
# subject
# on-label
# off-label

and return the soundbite in a user-friendly way so that the entry-level desk people (with little or no excel expertise) can retrieve it.

YES, it was not set up to do this, but this is what i've got. so i'm hoping that before i go down too many dead-ends, someone can tell me which is the best way to do this.

Looks like VLOOKUP can only really search on one column and filters would require too much expertise for entry-level employees, but should i look into VBA or Macros. or do i need to import this into Access -- or learn SQL to interface with it on a user-friendly website??

i'm happy to learn - it's what i do as the resident techie -- i just don't want to be learning the wrong application for the job. and they REALLY want to do this within Excel, if it's at all possible.

Please help if you can.

thanks,
dta

I am wondering if someone can point me in the right direction when it comes to using an excel sheet (which is already built and rather large) for functions which are more suited to database functions.

The excel contains audiotaped medical info in columns:
question
physician name
diagnosis/subject
timecode
label1 (checked box "on")
label2 (checked box "off")
comments

each of these are sectioned by audiotape with headers --
looking something like this --
TAPE 1
diagnosis/subject1
then under that, columns
question | physician name | timecode | label1 | label2

then
diagnosis/subject2
then under that columns
subject/question | physician name | timecode | label1 | label2

then
TAPE 2
with similar info
so each TAPE has numerous DIAGNOSES covered under which there are various questions asked and answered by individual doctors.
(see link to dummy file for visual)
http://markwilkinson.cihost.com/barr...edfile.xls.zip

I need to be able to search on a group of criteria simultaneously:
# physician’s name
# subject
# on-label
# off-label

and return the soundbite in a user-friendly way so that the entry-level desk people (with little or no excel expertise) can retrieve it.

YES, it was not set up to do this, but this is what i've got. so i'm hoping that before i go down too many dead-ends, someone can tell me which is the best way to do this.

Looks like VLOOKUP can only really search on one column and filters would require too much expertise for entry-level employees, but should i look into VBA or Macros. or do i need to import this into Access -- or learn SQL to interface with it on a user-friendly website??

i'm happy to learn - it's what i do as the resident techie -- i just don't want to be learning the wrong application for the job. and they REALLY want to do this within Excel, if it's at all possible.

Please help if you can.

thanks,
dta

OK, so...

IF i took out the blank cols and rows and had only one header row, how would you suggest should I then go about making this file searchable on multiple criteria. what options would I have in EXCEL?

here's the new file if that helps:
http://markwilkinson.cihost.com/barr...evised.xls.zip

thanks!
dta

Hi

So what do you do if you have multiple responses to the questions? How do you go to the next step? Eg Doctor A, Subject3, on lable and off lable are all blank so there are 3 items.

rylo

you would want ALL of the responses returned, since they, in audiofile order, answer the questions. They are split up into records merely for timecoding purposes.

thanks

that works except i have to then give them these instructions:

select all worksheet cells
drop down menu DATA/AUTOFILTER
then drop down from the arrows and select your criteria.

is is possible to just put a sheet with boxes for it to look like a data entry form like this?

As in:
Enter your criteria:
Doctor's name: ________
Diagnosis: ________
Off-label: _____
On-label: _____

and then send them to a sheet with answers (and i can pretty this up a bit to make it look like a clean list.

or would that require a macro?
(i'm on a mac, but they're on a PC - please confirm - macros no longer work on a mac in excel, correct, so i'd have to program this on PC?)

thanks in advance for sticking with me on this.

dta

With some time on my hands, I've had a play and come up with a little userform - see attached. I put some test values in the comboboxes which may not cover all your values but it may start you off.

Stephen,

You are the BOMB! i just returned from the weekend to find this Macro. What is easy for you is hair-raising for me. Thank you! (and i will go and rate you for it, of course)

Tell me, please, is there a source i can use which answers the rudimentary questions like "how do i run a macro?" "how do i write a basic excel Macro?" so i can make minor adjustments to your code?

again, many thanks!