Hi,
I'm new to this forum and VBA in general. However I've been designing a tool for a client that assists in the selection of engineered concepts by measuring the effectiveness vs associated risk.
Essentially the 1st segment of this spreadsheet consists of two components, a priority matrix and concept evaluation table.
1. The priority matrix assesses the criteria that will be used to measure the effectiveness of the concept. This is achieved by specifying the criteria, for eg. Cost, leakage sites, time to next investment cycle etc. Each of the criteria are compared with one another to assess the importance and impact they have to the success of the project/ concept. Therefore the priority matrix outputs a weight for each criteria.
2. Each concept is then assessed against the weighted criteria from a scale of -3 to 3, based on how well it exceeds the current datum (product) in meeting the criteria. An average is then taken on the score to output a concept effectiveness rating.
The sheet will then go onto assessing the technology risk of each concept and plot a graph displaying the concept effectiveness against the measured risk, to allow rational debate and selection.
Focusing on the first two components, I'm trying to have a functioning macro that will:
Allow the user to fill out a top level worksheet consisting of X number of systems on a column that have X number of concepts on each row. For eg. the rotor system, will have concept A,B,C,D etc. The Drive system will have concept A,B,C,D,E,F...etc, so on and so fourth. Hitting the macro button should then theoretically format the workbook, by creating two new worksheets for each system (a priority matrix and a concept evaluation chart). The concept evaluation sheet needs to reference and populate whatever criteria are put into the priority matrix. It also needs to populate the concept names from the top level sheet. The priority matrix sheet simply needs to be named the system name. So in essence all the templates are formatted, ready to be scored.
There may be situations where the criteria set for one system may be usable on another system, and therefore there would be no need to have a separate priority matrix for each other system. In an ideal world, if there is an option on the top level sheet to select whether a new criteria is needed for the system, would be ideal. Answering yes, should follow on to another question of whether the user would like to start from blank, or use a duplicate as a base. Answering no, will then result in only a concept evaluation sheet being generated and it taking all its references from the 1st priority matrix.
I realize this may be slightly confusing, but i hope someone can get a just of what i'm trying to do. Any help/ ideas in achieving this would be much appreciated.
Regards
I'm new to this forum and VBA in general. However I've been designing a tool for a client that assists in the selection of engineered concepts by measuring the effectiveness vs associated risk.
Essentially the 1st segment of this spreadsheet consists of two components, a priority matrix and concept evaluation table.
1. The priority matrix assesses the criteria that will be used to measure the effectiveness of the concept. This is achieved by specifying the criteria, for eg. Cost, leakage sites, time to next investment cycle etc. Each of the criteria are compared with one another to assess the importance and impact they have to the success of the project/ concept. Therefore the priority matrix outputs a weight for each criteria.
2. Each concept is then assessed against the weighted criteria from a scale of -3 to 3, based on how well it exceeds the current datum (product) in meeting the criteria. An average is then taken on the score to output a concept effectiveness rating.
The sheet will then go onto assessing the technology risk of each concept and plot a graph displaying the concept effectiveness against the measured risk, to allow rational debate and selection.
Focusing on the first two components, I'm trying to have a functioning macro that will:
Allow the user to fill out a top level worksheet consisting of X number of systems on a column that have X number of concepts on each row. For eg. the rotor system, will have concept A,B,C,D etc. The Drive system will have concept A,B,C,D,E,F...etc, so on and so fourth. Hitting the macro button should then theoretically format the workbook, by creating two new worksheets for each system (a priority matrix and a concept evaluation chart). The concept evaluation sheet needs to reference and populate whatever criteria are put into the priority matrix. It also needs to populate the concept names from the top level sheet. The priority matrix sheet simply needs to be named the system name. So in essence all the templates are formatted, ready to be scored.
There may be situations where the criteria set for one system may be usable on another system, and therefore there would be no need to have a separate priority matrix for each other system. In an ideal world, if there is an option on the top level sheet to select whether a new criteria is needed for the system, would be ideal. Answering yes, should follow on to another question of whether the user would like to start from blank, or use a duplicate as a base. Answering no, will then result in only a concept evaluation sheet being generated and it taking all its references from the 1st priority matrix.
I realize this may be slightly confusing, but i hope someone can get a just of what i'm trying to do. Any help/ ideas in achieving this would be much appreciated.
Regards
