Pestomania
Active Member
- Joined
- May 30, 2018
- Messages
- 313
- Office Version
- 365
- Platform
- Windows
I am working through a large excel sheet that I am hoping to get built into a Smartsheet at some point, but am working on a proof of concept in Excel first.
I have a document that has numerous tabs:
- Compiled
- Parent Processes
- Sub Processes
- Equipment
- Third Party
I cannot find a way to put the entire document on this through XL2bb so I have inputted through numerous different inserts below:
Each of the tabs have a "parent process" field that allows you to connect a certain row to a parent process. I am hoping to find a way to copy the data from the background tables and place them into the compiled tab (preferably with indentation and grouping) so that you can see it all together.
Here is an image of what I would really like to see in the end:
I really hope this will be possible.
I have a document that has numerous tabs:
- Compiled
- Parent Processes
- Sub Processes
- Equipment
- Third Party
I cannot find a way to put the entire document on this through XL2bb so I have inputted through numerous different inserts below:
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Parent Process 1: Open the door | |||||
2 | Sub-Process 1: Insert Key | |||||
3 | Equipment 1: House Keys | |||||
4 | Sub-Process 2: Turn Key | |||||
5 | Sub-Process 3: Turn Knob | |||||
6 | Sub-Process 4: Open Door | |||||
7 | Equipment 2: Front Doorway | |||||
8 | Sub-Process 5: Walk through door | |||||
9 | Sub-Process 6: Close door | |||||
10 | Sub-Process 7: Lock Door | |||||
11 | ||||||
12 | Parent Process 2: Do the laundry | |||||
13 | Sub-Process 8: Grab laundry hamper | |||||
14 | Equipment 4: Hamper | |||||
15 | Sub-Process 9: Walk hamper to washer | |||||
16 | Sub-Process 10: Open the washer lid | |||||
17 | Equipment 3: Washer | |||||
18 | Third Party 1 | |||||
19 | Third Party 2 | |||||
20 | Third Party 3 | |||||
21 | Sub-Process 11: Input laundry into washer | |||||
22 | Sub-Process 12: Grab detergent | |||||
23 | Sub-Process 13: Measure detergent | |||||
24 | Sub-Process 14: Pour detergent | |||||
25 | Sub-Process 15: Return detergent | |||||
26 | Sub-Process 16: Close lid | |||||
27 | Sub-Process 17: Turn washer knob | |||||
28 | Sub-Process 18: Select mode | |||||
29 | Sub-Process 19: Click Start Button | |||||
30 | ||||||
31 | Parent Process 3: Load the dishwasher | |||||
32 | Sub-Process 20: Open Dishwasher | |||||
33 | Equipment 5: Dishwasher | |||||
34 | Third Party 1 | |||||
35 | Third Party 2 | |||||
36 | Third Party 3 | |||||
37 | Sub-Process 21: Grab dishes | |||||
38 | Sub-Process 22: Place dishes in dishwasher | |||||
39 | Sub-Process 23: Grab dish detergent | |||||
40 | Sub-Process 23: Put dish detergent in dishwasher | |||||
41 | Sub-Process 23: Close detergent compartment | |||||
42 | Sub-Process 23: Close dishwasher load | |||||
43 | Sub-Process 23: Select Mode | |||||
44 | Sub-Process 23: Click Start Button | |||||
Compiled |
Each of the tabs have a "parent process" field that allows you to connect a certain row to a parent process. I am hoping to find a way to copy the data from the background tables and place them into the compiled tab (preferably with indentation and grouping) so that you can see it all together.
Book1 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Title | Description | Concat | ||
2 | Parent Process 1 | Open the door | Parent Process 1: Open the door | ||
3 | Parent Process 2 | Do the laundry | Parent Process 2: Do the laundry | ||
4 | Parent Process 3 | Load the dishwasher | Parent Process 3: Load the dishwasher | ||
Parent Processes |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:C4 | C2 | =CONCAT([@Title],": ",[@Description]) |
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Title | Description | Parent Process | Concat | ||
2 | Sub-Process 1 | Insert Key | Parent Process 1: Open the door | Sub-Process 1: Insert Key | ||
3 | Sub-Process 2 | Turn Key | Parent Process 1: Open the door | Sub-Process 2: Turn Key | ||
4 | Sub-Process 3 | Turn Knob | Parent Process 1: Open the door | Sub-Process 3: Turn Knob | ||
5 | Sub-Process 4 | Open Door | Parent Process 1: Open the door | Sub-Process 4: Open Door | ||
6 | Sub-Process 5 | Walk through door | Parent Process 1: Open the door | Sub-Process 5: Walk through door | ||
7 | Sub-Process 6 | Close door | Parent Process 1: Open the door | Sub-Process 6: Close door | ||
8 | Sub-Process 7 | Lock Door | Parent Process 1: Open the door | Sub-Process 7: Lock Door | ||
9 | Sub-Process 8 | Grab laundry hamper | Parent Process 2: Do the laundry | Sub-Process 8: Grab laundry hamper | ||
10 | Sub-Process 9 | Walk hamper to washer | Parent Process 2: Do the laundry | Sub-Process 9: Walk hamper to washer | ||
11 | Sub-Process 10 | Open the washer lid | Parent Process 2: Do the laundry | Sub-Process 10: Open the washer lid | ||
12 | Sub-Process 11 | Input laundry into washer | Parent Process 2: Do the laundry | Sub-Process 11: Input laundry into washer | ||
13 | Sub-Process 12 | Grab detergent | Parent Process 2: Do the laundry | Sub-Process 12: Grab detergent | ||
14 | Sub-Process 13 | Measure detergent | Parent Process 2: Do the laundry | Sub-Process 13: Measure detergent | ||
15 | Sub-Process 14 | Pour detergent | Parent Process 2: Do the laundry | Sub-Process 14: Pour detergent | ||
16 | Sub-Process 15 | Return detergent | Parent Process 2: Do the laundry | Sub-Process 15: Return detergent | ||
17 | Sub-Process 16 | Close lid | Parent Process 2: Do the laundry | Sub-Process 16: Close lid | ||
18 | Sub-Process 17 | Turn washer knob | Parent Process 2: Do the laundry | Sub-Process 17: Turn washer knob | ||
19 | Sub-Process 18 | Select mode | Parent Process 2: Do the laundry | Sub-Process 18: Select mode | ||
20 | Sub-Process 19 | Click Start Button | Parent Process 2: Do the laundry | Sub-Process 19: Click Start Button | ||
21 | Sub-Process 20 | Open Dishwasher | Parent Process 3: Load the dishwasher | Sub-Process 20: Open Dishwasher | ||
22 | Sub-Process 21 | Grab dishes | Parent Process 3: Load the dishwasher | Sub-Process 21: Grab dishes | ||
23 | Sub-Process 22 | Place dishes in dishwasher | Parent Process 3: Load the dishwasher | Sub-Process 22: Place dishes in dishwasher | ||
24 | Sub-Process 23 | Grab dish detergent | Parent Process 3: Load the dishwasher | Sub-Process 23: Grab dish detergent | ||
25 | Sub-Process 23 | Put dish detergent in dishwasher | Parent Process 3: Load the dishwasher | Sub-Process 23: Put dish detergent in dishwasher | ||
26 | Sub-Process 23 | Close detergent compartment | Parent Process 3: Load the dishwasher | Sub-Process 23: Close detergent compartment | ||
27 | Sub-Process 23 | Close dishwasher load | Parent Process 3: Load the dishwasher | Sub-Process 23: Close dishwasher load | ||
28 | Sub-Process 23 | Select Mode | Parent Process 3: Load the dishwasher | Sub-Process 23: Select Mode | ||
29 | Sub-Process 23 | Click Start Button | Parent Process 3: Load the dishwasher | Sub-Process 23: Click Start Button | ||
Sub-Processes |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D29 | D2 | =CONCAT(@[Title],": ",[@Description]) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C2:C29 | List | =Parent_Processes |
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
2 | Equipment 1 | House Keys | Sub-Process 1: Insert Key | Equipment 1: House Keys | ||
3 | Equipment 2 | Front Doorway | Sub-Process 4: Open Door | Equipment 2: Front Doorway | ||
4 | Equipment 3 | Washer | Sub-Process 10: Open the washer lid | Equipment 3: Washer | ||
5 | Equipment 4 | Hamper | Sub-Process 8: Grab laundry hamper | Equipment 4: Hamper | ||
6 | Equipment 5 | Dishwasher | Sub-Process 20: Open Dishwasher | Equipment 5: Dishwasher | ||
Equipment |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D6 | D2 | =CONCAT([@Title],": ",[@Description]) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C2:C6 | List | =SubProcesses |
Book1 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Title | Description | Parent Process | ||
2 | Third Party 1 | Electrical Company | Equipment 3: Washer & Equipment 5: Dishwasher | ||
3 | Third Party 2 | Water Company | Equipment 3: Washer & Equipment 5: Dishwasher | ||
4 | Third Party 3 | Costco | Equipment 3: Washer & Equipment 5: Dishwasher | ||
Third Party |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C2:C4 | List | =Equipment |
Here is an image of what I would really like to see in the end:
I really hope this will be possible.