hellblazeruk
asked on
Excel – workflow / help desk flow chart
Hi,
I am looking to create an excel based help desk flow chart for level 1 support where clicking yes or no to a question will divert the agent to the next question, does anyone have an examples or ideas how to do this?
I am looking to create an excel based help desk flow chart for level 1 support where clicking yes or no to a question will divert the agent to the next question, does anyone have an examples or ideas how to do this?
ASKER
Hi,
DO you want a graphical representation or will a text based steps? - Either
DO you want the Excel to move to the next question and highlight it? - Yes
Alex
DO you want a graphical representation or will a text based steps? - Either
DO you want the Excel to move to the next question and highlight it? - Yes
Alex
Here is a minimal sample to give you an idea.
I added buttons for the answers and recorded a macro for each which was simply selecting the next question then stopping the recording.
mlmcc
Book1.xlsm
I added buttons for the answers and recorded a macro for each which was simply selecting the next question then stopping the recording.
mlmcc
Book1.xlsm
A little change to the workbook, giving more flexibility.
The principle is using Data validation, answer Yes or No.
A roadmap (on the sheet RoadMap) give the next question based on either Yes or No.
Then changing the roadmap change the workflow, without changing any code.
The roadmap sheet can be hidden.
Worksheet change event (column C) trigger the code to run.
The code is in the worksheet module.
Question-Answers-move.xlsm
The principle is using Data validation, answer Yes or No.
A roadmap (on the sheet RoadMap) give the next question based on either Yes or No.
Then changing the roadmap change the workflow, without changing any code.
The roadmap sheet can be hidden.
Worksheet change event (column C) trigger the code to run.
The code is in the worksheet module.
Question-Answers-move.xlsm
Attached is a solution that uses a form, driven by a table.
The table can be constructed using a flowchart, and then the userform will follow the table in order to ask the next question.
A simplified example is attached.
In the Action (Yes) column, you would fill in the line number to go to next, if the user answers the current question as Yes.
In the Action (no) column you would fill in the line number to display next i fthe user answers the current question as no.
If you have a blank in either YES or NO column, then the buttons for Yes or no will not be visible.
HelpDesk.xlsm
The table can be constructed using a flowchart, and then the userform will follow the table in order to ask the next question.
A simplified example is attached.
In the Action (Yes) column, you would fill in the line number to go to next, if the user answers the current question as Yes.
In the Action (no) column you would fill in the line number to display next i fthe user answers the current question as no.
If you have a blank in either YES or NO column, then the buttons for Yes or no will not be visible.
HelpDesk.xlsm
The solutions provided so far are interesting.
Here are some other ideas to consider.
How will you add new questions? Will you just add entries to a sheet manually, or will there be some kind of dialog or form to assist?
Years ago, I found a web based "choose your own adventure" type game that is written in PHP. The idea is that each page presents the users with two choices. As the users go through the maze of questions, eventually they reach the end of the line. There is either an ending solution, or not. If there is no solution, then the users are encouraged to add one.
Here is a screen shot of the page at the end of the trail..
Here are some other ideas to consider.
How will you add new questions? Will you just add entries to a sheet manually, or will there be some kind of dialog or form to assist?
Years ago, I found a web based "choose your own adventure" type game that is written in PHP. The idea is that each page presents the users with two choices. As the users go through the maze of questions, eventually they reach the end of the line. There is either an ending solution, or not. If there is no solution, then the users are encouraged to add one.
Here is a screen shot of the page at the end of the trail..
here is a link to the main page if you want to look
Choose Your Own Adventure - Start Page
Choose Your Own Adventure - Add your story page
Choose Your Own Adventure - Start Page
Choose Your Own Adventure - Add your story page
ASKER
Thank you for the reply’s, some great example but the GUI interface from Ken is close to what I am looking to develop.
Ken,
I have already standard to add question to the table,
I have created a new screen before UserForm1 call Start where I plan to add 10 buttons to direct the user to the correct system before they answer questions
Example buttons: printer (step 10), phone (step 20), connection (step 30)
What code would I use under the button to update the current step and change the screen to UserForm1?
Ken,
I have already standard to add question to the table,
I have created a new screen before UserForm1 call Start where I plan to add 10 buttons to direct the user to the correct system before they answer questions
Example buttons: printer (step 10), phone (step 20), connection (step 30)
What code would I use under the button to update the current step and change the screen to UserForm1?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One additional note... the way it is coded right now... you cannot omit a step # in the step# column.... they have to stay sequential, and in order.
The reason for that is that it is treating the table like an array, and is using the action (yes) column values and the action (no) column values as indexes to the next row to display. it is not searching the entire column of "Step #" for a matching value. The code could be changed to search the column for a matching step #, but depending on the size of your final table, it could potentially slow things down a little.
At any rate, the way it is coded right now, you could delete values from Step # column and it would still work the same, because it's not really using them.... they are there right now only for reference when you build the table.
The reason for that is that it is treating the table like an array, and is using the action (yes) column values and the action (no) column values as indexes to the next row to display. it is not searching the entire column of "Step #" for a matching value. The code could be changed to search the column for a matching step #, but depending on the size of your final table, it could potentially slow things down a little.
At any rate, the way it is coded right now, you could delete values from Step # column and it would still work the same, because it's not really using them.... they are there right now only for reference when you build the table.
ASKER
Thank you
DO you want a graphical representation or will a text based steps?
DO you want the Excel to move to the next question and highlight it?
mlmcc