+ Reply to Thread
Results 1 to 6 of 6

Automated spreadsheet

  1. #1
    Registered User
    Join Date
    09-06-2011
    Location
    Guernsey
    MS-Off Ver
    Excel 2003
    Posts
    4

    Automated spreadsheet

    Hi there,
    I would like to create a spreadsheet to use when risk assessing clients. However, we deal with a variety of different clients and and each type has different questions. So instead of having a different sheet for each type, is it possible to programme one worksheet so that when you answer the first question (which will define the type of client) a specific list of questions will appear that require completing?
    Many thanks

  2. #2
    Registered User
    Join Date
    04-18-2011
    Location
    Surrey, UK
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Automated spreadsheet

    Hi ZRPhelps,

    Yes you can do this, and you might be able to do it using a formula rather than visual basic depending on what the format of the questions is.

    For example: Are the questions all similar format (e.g. all multiple choice with 4 answers) or are they all requiring a written response (e.g. a text box) or is there a combination?

    Are there choices within choices - if the client selects themselves as Type A, they get QuestionSet A, but is it possible that question 3 of QuestionSet A can lead to new questions being asked of them?

    Are you expecting just a general Excel worksheet that can be printed or are you expecting to use ActiveX forms?

    Kind regards,

    Paul

  3. #3
    Registered User
    Join Date
    09-06-2011
    Location
    Guernsey
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Automated spreadsheet

    Hi Paul,
    Thanks for your response.
    Some of the questions will require text boxes and some of them will have a drop-down Y/N which will determine a score (the total of which will risk assess them).
    For example, if the client is an individual, then a certain set of questions will appear as opposed to the client being a Company. So for example, Question 1 for an individual would be 1) Residence
    Question 1 for a Company would be 1) Place of Incorporation.
    And then they have comment boxes in the next cells along that can be manually populated.
    Answers to the questions won't generate a change in questions though, they will just add to the risk score calculation.
    Thank you,
    Zoe

  4. #4
    Registered User
    Join Date
    04-18-2011
    Location
    Surrey, UK
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Automated spreadsheet

    Hi Zoe,

    I think it would be useful to see some of the basic structure you have to the spreadsheet to really do anything useful - perhaps other people on the forum can help better.

    Anyway I had something in mind like the attached spreadsheet, which is obviously just a crude demo to show the kind of formulas your workbook could use. I think you can protect certain areas of the sheet to prevent people trying to manipulate them.

    Essentially I thought you could have two lists of questions and do a look up based on what their answer to Q1 is.

    If the attached spreadsheet is useless then I apologise in advance!

    Good luck!

    Paul
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-06-2011
    Location
    Guernsey
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Automated spreadsheet

    Hi Paul, thanks for the demo...much appreciated and will certainly be of help.
    And I definitely like beer...very much!
    Cheers,
    Zoe

  6. #6
    Registered User
    Join Date
    09-06-2011
    Location
    Guernsey
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Automated spreadsheet

    Okay, so have managed to get it working with two sets of questions, only problem is, I've got three options to choose from the drop down (Trust, Company or Managed Account) and therefore three sets of questions, I can't figure out how to make it distinguish between three options!?
    Any ideas?
    Thank you,
    Zoe
    Last edited by ZRPhelps; 09-08-2011 at 05:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1