VbaRookie_MyStation
New Member
- Joined
- Mar 28, 2022
- Messages
- 1
- Office Version
- 2016
- Platform
- Windows
So it's a little complicated what I need. Therefore bare with me 
.
I need to find a date which will be taken from a sheet called Raport. Cell where data validation of the date is "D4".
I need that date to be searched in multiple sheets. Each sheet is showing like this:
The problem is that based on selected date, on P2P or S2S, based on sheet name and based on shift pattern, I need to return the value of Target and Value of actual (which is in hh:mm:ss).
Conditions:
P2P and S2S are the columns where the dates are entered. If P2P is Empty it means it was used S2S and viceversa. (FYI.. This happens when its pressed a button, which means the time stamp is placed automatically)
P2P time could be 00:40:00 or 00:20:00, while S2S is always 00:14:00.
Sheet name si a fixed name L1_4701A, L2_4701B, L3_4701C, L4_4701D, L5_4701E, L6_4701F, L7_4701G, L8_4701H.
Each sheet in addition to its name has a date of the file when it was extracted e.g.: "L1_4701A 25_03"
Shift Patterns is
' Shift 3 - 22:00:01 - 00:00:00
' 00:00:01 - 06:00:00 (This is on the next day)
'Shift 1 06:00:01 - 14:00:00
'Shift 2 14:00:01 - 22:00:00
After selecting the date, I need to place the data according to which sheet name is (without date) L1_4701A, L2_4701B, L3_4701C, L4_4701D, L5_4701E, L6_4701F, L7_4701G, L8_4701H into another sheet, which is dedicated for collecting all data.
In Coloumn Target and Actual I need to sum of all items from Target and Actual based on shift patterns.
If it's not the right thread, I'll kindly ask you to tell me as this is my firs post.
Thank you !
.I need to find a date which will be taken from a sheet called Raport. Cell where data validation of the date is "D4".
I need that date to be searched in multiple sheets. Each sheet is showing like this:
| Raport_CHO.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | I | J | |||
| 1 | 20 | 40 | 14 | Target | Actual | C/O Performance | P2P | S2P | ||
| 2 | 3/24/2022 9:13 | 3/24/2022 9:13 | 3/25/2022 9:13 | 1:14:00 | 0:00:02 | 0.05% | ||||
| 3 | 3/25/2022 9:13 | 3/25/2022 9:13 | 0:20:00 | 0:00:00 | 0.00% | 3/25/2022 9:13 | ||||
| 4 | 0:34:00 | 0:00:01 | 0.05% | 3/25/2022 9:13 | ||||||
| 5 | 1:14:00 | 0:00:02 | 0.05% | 3/25/2022 9:13 | ||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 | ||||||||||
L1_4701A 25_03 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2:H10 | H2 | =IFERROR(G2/F2,"") |
The problem is that based on selected date, on P2P or S2S, based on sheet name and based on shift pattern, I need to return the value of Target and Value of actual (which is in hh:mm:ss).
Conditions:
P2P and S2S are the columns where the dates are entered. If P2P is Empty it means it was used S2S and viceversa. (FYI.. This happens when its pressed a button, which means the time stamp is placed automatically)
P2P time could be 00:40:00 or 00:20:00, while S2S is always 00:14:00.
Sheet name si a fixed name L1_4701A, L2_4701B, L3_4701C, L4_4701D, L5_4701E, L6_4701F, L7_4701G, L8_4701H.
Each sheet in addition to its name has a date of the file when it was extracted e.g.: "L1_4701A 25_03"
Shift Patterns is
' Shift 3 - 22:00:01 - 00:00:00
' 00:00:01 - 06:00:00 (This is on the next day)
'Shift 1 06:00:01 - 14:00:00
'Shift 2 14:00:01 - 22:00:00
After selecting the date, I need to place the data according to which sheet name is (without date) L1_4701A, L2_4701B, L3_4701C, L4_4701D, L5_4701E, L6_4701F, L7_4701G, L8_4701H into another sheet, which is dedicated for collecting all data.
| Raport_CHO.xlsm | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | |||
| 2 | L1_4701A | Data | Shift1 | Shift2 | Shift3 | |||||||||||
| 3 | Target | Actual | P2P | S2P | Target | Actual | P2P | S2P | Target | Actual | P2P | S2P | ||||
| 4 | 3/25/2022 | |||||||||||||||
| 5 | 3/26/2022 | |||||||||||||||
| 6 | 3/27/2022 | |||||||||||||||
| 7 | 3/28/2022 | |||||||||||||||
| 8 | 3/29/2022 | |||||||||||||||
| 9 | 3/30/2022 | |||||||||||||||
| 10 | 3/31/2022 | |||||||||||||||
| 11 | 4/1/2022 | |||||||||||||||
InformatiiColectate | ||||||||||||||||
In Coloumn Target and Actual I need to sum of all items from Target and Actual based on shift patterns.
If it's not the right thread, I'll kindly ask you to tell me as this is my firs post.
Thank you !
