Creating a "Flow Chart" Parameter Document

[ianDDS]

Hello All,

I am currently a dental student and we need to know a lot about Oral Pathology.

What I want to create is a spreadsheet that can be used for differential diagnosis. What I mean by this is that I want to create a spreadsheet with a set of findings such as "inflammation", "Radiolucency" "Circumscribed Borders", etc.

Once the parameters are created, I want to make it so that you can select one parameter and it will show all of the pathologies that it can be. Then you can select a second parameter and it narrows the pathologies down to those that match both parameters, then a third, and a fourth, and so on.

Please let me know if this is possible through a Macro or another formula and how to go about doing this.

Thank you!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

It sounds like you might be able to use an AutoFilter.

You could make a list of the pathologies in column A then have a separate column for each of the findings.
Against each pathology you would need to indicate which findings apply. Pick a symbol, an X say, and put it in every applicable column.

Now select all the columns.
Then on the Home tab find the Editing section and select "Sort & Filter".
From there select "Filter".

You will now see a set of dropdowns for each column. If you click on any one you will be able to select just the X's to hide the non-relevent rows. Selecting another dropdown in another column will let you home on on a solution.

 

[ianDDS]

Thank you Rick. Now what if I wanted to have each parameter going across a column and underneath it a yes/no drop down. Then once all drop downs are selected have an answer function that drops down all of the possible pathologies. So essentially creating an "and" "or" for the parameters for each pathology to spit out an answer(s)?

 

[RickXL]

Hi,

Sorry, I didn't quite follow that.

The AutoFilter functionality will give you the AND capability. When you have selected Yes or No ( I originally proposed X or blank) you will be left looking at a list of relevant pathologies.

Can you give more details about what your "answer function" should do?

 

[ianDDS]

Here is the link of what I am looking for: https://www.dropbox.com/s/nc7j8fc728p7yzq/Screenshot 2015-09-11 18.17.06.png?dl=0

As you go across the column I want to be able to select yes or no for the parameters and then have an output with the correct answer.

Also, lets just say I only wanted to know the pathologies associated with inflammation, I want to be able to just put yes for inflammation and leave the other options blank to see what pathologies that could be.

Does that make more sense?

 

[RickXL]

OK, let's try and consolidate.

Have you tried my AutoFilter suggestion?

I think it does what you want with the exception of the Pathology Dropdown. Effectively, it makes the dropdown redundant because all the possibilities will already be displayed. As soon as you make your selection the data will be displayed with no need to have an extra dropdown feature.

You could use the same technique but hide the Pathology column. You could then make the AutoFilter selection and a macro could extract the data from the hidden column and add it to a control so that you could press a button to display it. It does seem easier to just let it display it as it goes along, though.