ForgetfulNerd
New Member
- Joined
- Nov 10, 2021
- Messages
- 1
- Office Version
- 2019
Hello,
I've been trying to automate some reports that i make on weekly basis. I'm quite stumped with this one. Would appreciate it someone could point me in the right direction.
I'm making the report based on Sheet-Data.
Tried the Index match in first column to detect No from data but it does not show the next value.
Below is the raw data i'm using
I've been trying to automate some reports that i make on weekly basis. I'm quite stumped with this one. Would appreciate it someone could point me in the right direction.
I'm making the report based on Sheet-Data.
Tried the Index match in first column to detect No from data but it does not show the next value.
Book1 | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
1 | Week | Work date | Checked On | Tag ID | User | QA Tag | QA Name | Supervisor ID | Supervisor | Manager ID | Manager | Assigned | Used | Total Audited | Correct Adherence # | Incorrect Adherence #2 | Adherence % | Tag Adherence | Login Remarks | ||
2 | Week1 | 01-11-2021 | 04-11-2021 | 38551 | Ashish | 158 | Aslam Shaikh | 17241 | Joseph | 3006 | James Eiker | SSRJGH | SSROKH | 7 | 6 | 1 | 85.71% | FALSE | |||
3 | Week1 | 02-11-2021 | 05-11-2021 | 44852 | Veena | 625 | Ramniwash Singh | 15813 | Abinesh | 3006 | James Eiker | SSRVNF | SSRROJ | 2 | 1 | 1 | 50.00% | FALSE | |||
Report |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Q2:Q3 | Q2 | =O2/N2 |
R2:R3 | R2 | =[@Assigned]=[@Used] |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
A2:A3 | List | Week1, Week2, Week3, Week4, Week5 |
Below is the raw data i'm using
Book1 | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Tag ID | User | Assigned Tag | Work Date | Supervisor ID | TL Name | Audit Date | Overall Status | QA ID | QA | Weeks | Adhrence | Tag used | ||
2 | 38551 | Ashish | SSRJGH | 01-10-2021 | 17241 | Joseph | 04-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | No | SSROKH | ||
3 | 38551 | Ashish | SSRJGH | 01-10-2021 | 17241 | Joseph | 04-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | Yes | SSRJGH | ||
4 | 38551 | Ashish | SSRJGH | 01-10-2021 | 17241 | Joseph | 04-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | Yes | SSRJGH | ||
5 | 38551 | Ashish | SSRJGH | 01-10-2021 | 17241 | Joseph | 04-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | Yes | SSRJGH | ||
6 | 38551 | Ashish | SSRJGH | 01-10-2021 | 17241 | Joseph | 04-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | Yes | SSRJGH | ||
7 | 38551 | Ashish | SSRJGH | 01-10-2021 | 17241 | Joseph | 04-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | Yes | SSRJGH | ||
8 | 38551 | Ashish | SSRJGH | 01-10-2021 | 17241 | Joseph | 04-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | Yes | SSRJGH | ||
9 | 44852 | Veena | SSRVNF | 01-10-2021 | 625 | Ramniwash Singh | 04-10-2021 | Pass | 625 | Ramniwash Singh | Week1 | Yes | SSRVNF | ||
10 | 44852 | Veena | SSRVNF | 01-10-2021 | 625 | Ramniwash Singh | 04-10-2021 | Pass | 625 | Ramniwash Singh | Week1 | Yes | SSRVNF | ||
11 | 44852 | Veena | SSRVNF | 01-10-2021 | 625 | Ramniwash Singh | 04-10-2021 | Pass | 625 | Ramniwash Singh | Week1 | Yes | SSRVNF | ||
12 | 44852 | Veena | SSRVNF | 01-10-2021 | 625 | Ramniwash Singh | 04-10-2021 | Pass | 625 | Ramniwash Singh | Week1 | Yes | SSRVNF | ||
13 | 44852 | Veena | SSRVNF | 01-10-2021 | 625 | Ramniwash Singh | 04-10-2021 | Pass | 625 | Ramniwash Singh | Week1 | Yes | SSRVNF | ||
14 | 38551 | Ashish | SSRJGH | 02-10-2021 | 17241 | Joseph | 05-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | No | SSRJGH | ||
15 | 38551 | Ashish | SSRJGH | 02-10-2021 | 17241 | Joseph | 05-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | Yes | SSRJGH | ||
16 | 38551 | Ashish | SSRJGH | 02-10-2021 | 17241 | Joseph | 05-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | Yes | SSRJGH | ||
17 | 38551 | Ashish | SSRJGH | 02-10-2021 | 17241 | Joseph | 05-10-2021 | Pass | 158 | Aslam Shaikh | Week1 | Yes | SSRJGH | ||
18 | 44852 | Veena | SSRVNF | 02-10-2021 | 625 | Ramniwash Singh | 05-10-2021 | Pass | 625 | Ramniwash Singh | Week1 | No | SSRROJ | ||
19 | 44852 | Veena | SSRVNF | 02-10-2021 | 625 | Ramniwash Singh | 05-10-2021 | Pass | 625 | Ramniwash Singh | Week1 | Yes | SSRVNF | ||
Data |