Help Creating In House Reporting Excel Doc

[Joey Bean]

Hi all,

This is my first post so I hope you can help me. I hope I'm not asking too much but I'm completley stuck with this one now. I have basic knowledge on Excel and I need to create a document to monitor some of my staff at work. Basically it’s the fine tuning of the final stages of the document.

I need to be able to monitor if our Knowledge Tool (KT) is being used correctly by staff and so I have created an Excel document to do this. The document has been designed so that it will automatically update from a Master Staff list so that the names listed will always be up to date. This part of the document is working fine.

(Knowledge Tool is basically our version of Google if you will, it tells staff exactly what is needed for the call they’re working on so when it isn’t used or used incorrectly we have to sort out the errors)

There are 2 tabs in the document, 'Master' where all staff's names are kept, fed in from another sheet. This then links to the 'Totals' tab where the scoring is recorded. This is where I have a problem. I would like the 3 Questions that have been asked to have drop down selections of Yes and No, at the moment you have to manually type the answer of Yes and No. Also the scoring system is messed up and I can't figure out how to correct it.

The First Question is - Should KT have been used?
Second Question is - Was KT used?
Third Question is - Was the correct KT Document used?

The only possible reply for the first question is Yes or No, if the answer is Yes then the next 2 questions need to also be answered. Again, with a Yes or No response. This all feeds into the Totals table and gives a percentage score for the staff member. There is a flaw here where I decided to have the table automatically answer Question 2 & 3 with a N/A response if the answer to question 1 was No. This then has a knock on effect where you can then overwrite questions 2 & 3 with Yes or No and in turn this can throw the marking system out and give a percentage score of more then 100%, which obviously isn't correct.

In short, I need the answers on the table to be a drop down selection boxes rather than manually typing the answers in but the formulas still in working order. Also the working out on the totals to be such that you are unable to manipulate the scores to more than 100%. Somehow I need to totally remove the questions 2 & 3 from the scoring system that become N/A answer due to question 1’s response being a No and in turn I think this will mean you can’t get more than 100% score.

Any help would be greatly appreciated.

Thanks

Joe

[manny_cb]

not sure how ur marking works.. a yes to question 1 is how many marks and a yes to question 2 is how many??

about the drop down menu..

somwhere in ur sheet u can write Yes and No in tow cells in a column
then go to ur question1 first cell.. choose data >validation >list .. choose those two cells..

now u can only select yes and no in dat cell...
google or search data validation on this forum.. should be able to find help on ir easily

dat should get u goin somewhere...

[Joey Bean]

Hi Manny,

Thanks for the reply. Apologies forgot to state how the marking system works as this would have been helpful. Question 1 is not worth any points as this just acts as an indicator to make questions 2 & 3 live. Questions 2 & 3 are worth a point each. So if all 10 sections are live (Question 1 response Yes) there will be a possible 20 marks available. If any of the 10 sections Question 1 are a No response, then the 2 follow up questions (2 & 3) will be removed from the overall score. So if just the 1 section out of the whole 10 was removed that would leave a possible 18 marks.

Hope thanks makes sense, I confuse myself at times.

I'll have a look at the 'Tow cells' part now.

Cheers