table sorting mutiple values

[jeeper74]

Ok, I am working on a sheet for my wife. What is needed is a form for medical kind of stuff. So I will give my best description but I have never done anything quite like this before.

So I started with making a table for this project, it included headers for "common name, latin name, part used, biochemical actions, safety issues, dosage" and then a long list of data under that. I want to be able to retain the ability of sorting like the table allows for names, but the part i cant figure out how to do is:

The biochemical actions coulm will contain several entries for each item on the list. How can you sort each action when there are mutiple? for example

cell a2="aloe"
cell a5="whatever aloe does #1, something else it does #2"

Then if I select the drop down list from Biochemical actions, I get select all or (aloe#1, aloe#2) as a joint option, I cant select just one item.

This is not the only spot I will need this ability either, she would like to be able to reverse look up entries, like pick a condition and things pertaining to that would come up.

So hopefuly I have explained this well enough...
If it has to be done on different sheets that would work just fine as long as the data could be updated from one location.
Guide me oh wise excel masters

[FDibbins]

Hi

so

cell a5="whatever aloe does #1, something else it does #2"

is all in 1 cell?

Would it be possible to put #1 in 1 row and #2 in another row, and the duplicate all the rest of the data from #1?

Would it be possible to upload a sample workbook (w/o sensitive info) to look at?

[jeeper74]

Yes, I was trying to enter all of the properties or info about that item into one cell. That is why the issue when using filters. I will try to get a sample of what I want worked up.

[jeeper74]

ok, here is a sample of what I started with... The biochemical column has 4 entries in it that I would like to be able to select one of, not all 4 as a group.

sample.xls

There will be between 300 and 500 entries, each with its own information. So if something was good for a cold or hangover or whatever, being able to sort like that for each of those columns that have mutiple entries. Or is this going to be to complex of a project for excel?

[FDibbins]

Thanks for the file. The way you have the data entered in E, you will not be able to sort on seperate parts of the contents Its a bit like asking excel to sort based on a singer letter in a cell.

However, you *may* be able to use filters on your data, and filter on a spefic word? I dont have 2003, and I cant remember how the filtering works (Im at work, I have 2003 & 2007 at home, if you can wait a few hours?)

[jeeper74]

no rush time wise, this will be a work in progress over the next few months as the data gets entered. Is there a way I could do the sorting like i want via a DB somehow? I dont know anything about that area though...

[FDibbins]

The only way I can think of would be to have each "Action" in its own cell, but let me look againt later tonight at home on 2003 (you DO have 2003, right?)

[mehmetcik]

hi

I guess you know that you should be using a data base for this.

But I think what you want can be easily done using excel.

Using a Userform as your user interface.

Can you wait ten minutes while I knock up something?

[jeeper74]

I am running 07 excel, under WINE in linux. Ive got something off on the install and it wont let me use xlsx files. I will deal with that later...

Wating patiently to see what you guys come up with.

[mehmetcik]

Ok

I am not on my pc so do not have access to by data,

However the attached may give you an idea of what I am visualising.

The idea is that you can sort by Medicinal Use, by Drug, by active ingredient
and get useful information.

Browse this and let me know if its worth progressing.

[jeeper74]

Well that most definently has potental. I had to boot the computer into windows to get the VB to work... Some things just dont work in linux. I think I will start with making a form that I like for it.

I haven't done much vb programming, why did the list not line up with what was selected in the user form? antiinflammatory was what I had to select to get weed and aloe, but on the workbook it was under immunostimulant? everything shifted one column over...

[mehmetcik]

I wouldn't let that worry you at this stage.

It was probably my rough programming.

I'm going to expand on my work a bit.

I'm Thinking that after we have designed this userform, then we should work on one to help your data input.

[mehmetcik]

The attached file fixes the problem that you highlighted.

[mehmetcik]

Hi

Ok Play around with this.

Add more Data so we can develop it.

To see the Userform at its best Select "By Active Ingredients" on Top Left

Then type "ami" in the text box that says "Type here to Minimise Search".

The two boxes on the right do not do anything yet. The box on bottom left just displays a list at the moment.

Enjoy

[jeeper74]

10-4 I will work on this tonight.
Thanks for your help!

[jeeper74]

ok, I think I may have stumbled on a different way to use filters than I had used before... I think that with just simply using custom filters and searching one direction ( like that herbs help this condition, or what conditions this herb helps) at a time, it will work. I have played with the database file you gave me, and worked with the forms for a while but just couldnt get anything that I was happy with. I didnt really want to spend the next three years of my life learning to program so I could do this. So thanks for your help with this but I am going to just go with the simple filter for now. If I change my mind in the future then I will have all the info already on a form so you guys will have something to play with.

Thanks