Formulas Based on results from a drop down menu

[bramdana]

Hey all,

An interesteing one for you here.

I am building a sheet that helps managers at our company decipher information that is given to them on a daily workbook I created.

I want to have a drop down menu in Col J, that refers to the major questions they will ask.

Time started (Before)
Time Finished (After)
Number of Cards (More than)
Stops Per hour (more than)
Amount of Time Taken (More Than)

I have set up in Col (K) a secondary drop-down list which based on the question selected in (J) will offer options for the answer E.G Time questions will make (K) refer to options based on time and selections based on amounts will offer numbers as an answer.

The elements in brackets are the parts of the formula that create the search option. E.G If select 'Time Started' it will search for all records that started before the time selected in the next col (K)


Right if that makes sense.. I have completed this. so I have the following: A question drop down list in (J), and an answer drop down in (K) which is dependant on the type of question asked. (I have attached an example)

I want to now have a totals box after the question where it displays a total amount of cols that are true to the details in the question.


After that (if possible) I will want to use conditional formatting to highlight the ones that reflect to the question.



Realistically if possible i wouldnt mind creating a question box, a box that allows them to select 'more than', 'exact' or 'less than' and then the specific value.
Basically a conditional formatting box for dummies within the worksheet. Attached is a demonstration. Any thoughts would be great thank you

[bramdana]

any thoughts please guys?

[FDibbins]

Hi and welcome to the forum

Nice set-up so far!!

Not sure how you get 41, but maybe this will get you headed in the right directions. I used this to count the number of entries (based on your data and selections) that were <= 5...
=COUNTIF(INDIRECT(J2),"<="&K2)

[jaslake]

Hi bramdana

Also...Welcome to the Forum!!

Here's a UserForm approach with VBA. You'll probably want to change to strictly Military Time the Row Source for "People Who Finished".

The Code runs from a Button on Sheet1

Please Login or Register  to view this content.

[bramdana]

Cheers Gents.

I hadnt really played with VBA but jaslake thats brilliant ty for the sample aswell.

Fdibbins - excel formulas were the way i wanted to go and you have helped, still some niggling issues which i will try but many thanks for your help!

feel free to give more ideas guys we are all always learning!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.