excel - Scenario Builder using VBA

Question

I have a very complex problem that may need your expert help

I want to create a scenario builder, based on separate sheet on the same workbook that has all possible scenarios, this sheet is the transformation of business workflow, each task in this workflow has many actions on it, and each action lead to a different scenario, and each action has its own status for the same task, so the combination of (Task, Action, and status) is always unique.

Sheet 1 (All Scenarios)

Sheet 2 (Scenario builder)

I’ve created this sample excel sheet to demonstrate what I need, (Sheet 1) is the main sheet that has all possible scenarios, (Sheet 2) is the Scenario builder, the first and second rows in (Sheet 2) is always fixed, but the actions in third row D3 is a drop-down list (Data validation) that won't change.

based on the action selected by me on D3, the code must search for selected (Action) value on (Sheet 1) column (D) and display corresponding (out-status ID, Out-status, SLA) on the same row only after validating that out-status of the previous row {of the used drop-down} in (Sheet 2) = in-status in (Sheet 1)

so for example if I select (Action #3) on the drop-down menu in (Sheet 2) a search service must initiate in (sheet 1) to look-up for (Action #3) on column D, then display {Out-status ID, Out-status, SLA of sheet 1 on corresponding cells in sheet 2} after validating that out-status of the previous row {of the used drop-down} in (Sheet 2) F2:{in status #xyz}= in-status in (Sheet 1) G6:{in status #xyz}, the displayed out-status will be {In-status #4} and its code and SLA and task ID, Task name Role all copied from the same row of out-status on Sheet 1 to the corresponding row in sheet 2

Selected action

display (task ID, Task name, role, and actions) of the next task, next task will be identified by copied (out status) of the previous task in sheet 2, a service must search on copied (out-status) on the last step in sheet 2, on (in-status) column in (Sheet 1), you can use (in-status ID) and it'll match with multiple rows, I want the service to copy the first values found of the corresponding (in status), there are multiple duplicates (in-status) because there's unique in-status for each action and there’re many actions in each task

then I want to create a dropdown of all possible actions corresponded to out-status of (Sheet 2) = (in-status of sheet 1) that the user can select any value from it and then loop the cycle, hint: all possible action drop-down list could be identified by task ID too, but the list will have many duplicates, and you’ll have to display the unique values only I know it’s a very complex problem, but it’ll help me so much.

Plus I’ve little knowledge in VBA, so it’ll be impossible to build such a complex code by myself

Could anyone help me with this problem???

标签： excelvba