L
Legacy 489849
Guest
Dear internet,
I am currently building an Excel tool for my bachelor's thesis and I am kinda lost as I seem to stupid to figure out (to me) more complex VBA. This is like the last thing I need to be done with this tool, so help is highly appreciated!
So I have a sheet (INPUT DATA) with the structure as shown in the Excel file attached. The aim is to basically get a report with two lists based on conditions met in column B and C with content from column D and E in another sheet (OUTPUT DATA). It should indicate all measures a user has not fulfilled yet in two categories "Mandatory measures" and "Recommended measures".
Which measures are recommended and which ones mandatory changes dynamically based on user input. The idea is to have the user click a button once he is done with all input (which is basilly filling out a questionnaire ticking boxes with yes/no where I do some clean up of input), so the list is fully set up with no more changes to generate the report with the two lists.
My idea was to loop through every row and check first: if B (Done?) = FALSE and C (Category) = Mandatory --> place cell value from D (Headline) in next empty cell and E (Text) in the one following
Until there are no more cells in INPUT DATA filled with values (which is about 100 rows in total)
Once done for all mandatory measures, same should be done for the recommended measures with a headline indicating the change in between (see OUTPUT DATA for inteded formatting). So again if B (Done?) = FALSE and C (Category) = Recommended --> place cell value from D (Headline) in next empty cell in OUTPUT DATA list and E (Text) in the one following
I have googled quite a qhile and watched some tutorials on VBA, but hell, I just can't figure it out
- thank you very much!
The INPUT SHEET looks smiliar to this:
The OUTPUT SHEET should be something like this:
I am currently building an Excel tool for my bachelor's thesis and I am kinda lost as I seem to stupid to figure out (to me) more complex VBA. This is like the last thing I need to be done with this tool, so help is highly appreciated!
So I have a sheet (INPUT DATA) with the structure as shown in the Excel file attached. The aim is to basically get a report with two lists based on conditions met in column B and C with content from column D and E in another sheet (OUTPUT DATA). It should indicate all measures a user has not fulfilled yet in two categories "Mandatory measures" and "Recommended measures".
Which measures are recommended and which ones mandatory changes dynamically based on user input. The idea is to have the user click a button once he is done with all input (which is basilly filling out a questionnaire ticking boxes with yes/no where I do some clean up of input), so the list is fully set up with no more changes to generate the report with the two lists.
My idea was to loop through every row and check first: if B (Done?) = FALSE and C (Category) = Mandatory --> place cell value from D (Headline) in next empty cell and E (Text) in the one following
Until there are no more cells in INPUT DATA filled with values (which is about 100 rows in total)
Once done for all mandatory measures, same should be done for the recommended measures with a headline indicating the change in between (see OUTPUT DATA for inteded formatting). So again if B (Done?) = FALSE and C (Category) = Recommended --> place cell value from D (Headline) in next empty cell in OUTPUT DATA list and E (Text) in the one following
I have googled quite a qhile and watched some tutorials on VBA, but hell, I just can't figure it out
The INPUT SHEET looks smiliar to this:
| Excel_VBA_Dynamically get values from sheet to another (1).xlsx | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | User input | Done? | Category | Headline | Text | ||
| 2 | Yes | WAHR | Recommended | Use of passwords | Use passwords. | ||
| 3 | No | FALSCH | Mandatory | Lockable rooms | Lock rooms. | ||
| 4 | No | FALSCH | Recommended | Fire detection | Get some fire protection. | ||
| 5 | Yes | WAHR | Mandatory | Do stuff | Do something else | ||
| 6 | No | FALSCH | Mandatory | Do more stuff | Do another thing | ||
| 7 | No | FALSCH | Recommended | Have a good time | Enjoy your day | ||
| 8 | … | ||||||
INPUT DATA | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B7 | B2 | =IF(A2="Yes", TRUE, FALSE) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| A2:A8 | List | Yes; No |
The OUTPUT SHEET should be something like this:
| Excel_VBA_Dynamically get values from sheet to another (1).xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Click this button once you are done for your report: | Some Excel Button to trigger Macro running | |||||||
| 2 | |||||||||
| 3 | |||||||||
| 4 | The following measures MUST be taken into account: | ||||||||
| 5 | Lockable rooms | ||||||||
| 6 | Lock rooms. | ||||||||
| 7 | Do more stuff | ||||||||
| 8 | Do another thing | ||||||||
| 9 | |||||||||
| 10 | The following measures SHOULD be talken into account: | ||||||||
| 11 | Fire detection | ||||||||
| 12 | Get some fire protection. | ||||||||
| 13 | Have a good time | ||||||||
| 14 | Enjoy your day. | ||||||||
| 15 | |||||||||
| 16 | |||||||||
OUTPUT DATA | |||||||||
