nelsonsix66
New Member
- Joined
- Dec 19, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
I have created a work log for my team which can monitor how much work is currently being put through the business, who completed the task and how long it took to complete.
The initial work request gets entered on to the ready to write tab and then once complete the whole row is cut and pasted onto the Report completed tab – this is the tab the MI is pulled from. This cut and paste brings with it the formula and complexity score calc which is important.
The important information is the Primary and secondary work columns which calculates the complexity score from a vlook up (on a seperate tab), the PP name which is the person who has completed the work, and the date passed back to adviser which helps me calculate the turn around time for each piece of work.
I have a separate spreadsheet for MI that pulls from the attached spreadsheet – this works fine as long as the data in entered correctly on the main log.
There are about 50 people at any one time adding, editing or looking at the shared spreadsheet and I have found that there are a lot of issues occurring, for example formulas being lost/deleted, rows being inserted, and the formulas not automatically being included in the new row.
This has meant that data is not being fed through to my separate MI spreadsheet and I am constantly having to edit data to make sure it pulls through correctly, this is not ideal as it can be time consuming, and the point of this log was to automate a lot of the data coming through the business.
I appreciate that it isn’t a particular complex spreadsheet, but I would like it to work correctly all the time and any ways in which I can stop people messing up formulars would be great. I was thinking of having a hidden tab in which does all the calculation, so when the row is moved to the completed tab, the formulas don’t need to be copied and pasted. Rows Q to T are usually hidden.
Any ideas would be great.
Thanks
The initial work request gets entered on to the ready to write tab and then once complete the whole row is cut and pasted onto the Report completed tab – this is the tab the MI is pulled from. This cut and paste brings with it the formula and complexity score calc which is important.
The important information is the Primary and secondary work columns which calculates the complexity score from a vlook up (on a seperate tab), the PP name which is the person who has completed the work, and the date passed back to adviser which helps me calculate the turn around time for each piece of work.
I have a separate spreadsheet for MI that pulls from the attached spreadsheet – this works fine as long as the data in entered correctly on the main log.
There are about 50 people at any one time adding, editing or looking at the shared spreadsheet and I have found that there are a lot of issues occurring, for example formulas being lost/deleted, rows being inserted, and the formulas not automatically being included in the new row.
This has meant that data is not being fed through to my separate MI spreadsheet and I am constantly having to edit data to make sure it pulls through correctly, this is not ideal as it can be time consuming, and the point of this log was to automate a lot of the data coming through the business.
I appreciate that it isn’t a particular complex spreadsheet, but I would like it to work correctly all the time and any ways in which I can stop people messing up formulars would be great. I was thinking of having a hidden tab in which does all the calculation, so when the row is moved to the completed tab, the formulas don’t need to be copied and pasted. Rows Q to T are usually hidden.
Any ideas would be great.
Thanks
| Test Log.xlsx | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 5 | Office | Client Name | Date Added | Primary Work | Secondary Work | Additional Comments (BRIEF NOTES) | Fee (£) | Adviser | PP | Date Started | Date Passed back to adviser | Complexity Score | |||||||||
| 6 | Bond Withdrawal | Addendum | 01/12/2024 | 6.5 | 5 | 1.5 | 12/24 | ||||||||||||||
| 7 | Bond Withdrawal | N/A | 01/11/2024 | 5 | 5 | 0 | 11/24 | ||||||||||||||
| 8 | Cashflow Planning | N/A | 01/01/2025 | 4 | 4 | 0 | 01/25 | ||||||||||||||
| 9 | Investment Review | Investment Top Up | 10/10/2024 | 9 | 8 | 1 | 10/24 | ||||||||||||||
| 10 | 01/00 | ||||||||||||||||||||
| 11 | 01/00 | ||||||||||||||||||||
| 12 | 01/00 | ||||||||||||||||||||
| 13 | 01/00 | ||||||||||||||||||||
| 14 | 01/00 | ||||||||||||||||||||
| 15 | 01/00 | ||||||||||||||||||||
| 16 | 01/00 | ||||||||||||||||||||
| 17 | 01/00 | ||||||||||||||||||||
| 18 | 01/00 | ||||||||||||||||||||
| 19 | 01/00 | ||||||||||||||||||||
| 20 | 01/00 | ||||||||||||||||||||
| 21 | 01/00 | ||||||||||||||||||||
| 22 | 01/00 | ||||||||||||||||||||
Ready to Write | |||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| P6:P22 | P6 | =IF(E6="","",VLOOKUP(E6,List!$F$3:$G$29,2,FALSE)) |
| Q6:Q22 | Q6 | =IF(F6="","",VLOOKUP(F6,List!$H$3:$I$31,2,FALSE)) |
| R6:R22 | R6 | =TEXT(M6,"MM/YY") |
| O17,N6:N22 | O17 | =IF(G17="","",Q17+R17) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| N6:N209,O17 | Cell Value | between 0 and 4 | text | NO |
| N6:N209,O17 | Cell Value | between 4 and 10 | text | NO |
| N6:N209,O17 | Cell Value | between 10.1 and 1000 | text | NO |
| K6:K209 | Cell Value | contains "KM" | text | NO |
| K6:K209 | Cell Value | contains "SH" | text | NO |
| K6:K209 | Cell Value | contains "LP" | text | NO |
| K6:K209 | Cell Value | contains "DM" | text | NO |
| K6:K209 | Cell Value | contains "AJ" | text | NO |
| K6:K209 | Cell Value | contains "SW" | text | NO |
| K6:K209 | Cell Value | contains "DN" | text | NO |
| K6:K209 | Cell Value | contains "NH" | text | NO |
| K6:K209 | Cell Value | contains "JB" | text | NO |
| K6:K209 | Cell Value | contains "TBC" | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| E6:E209 | List | =List!$F$3:$F$29 |
| F6:F209 | List | =List!$H$3:$H$31 |
| J6:J209 | List | =List!$N$13:$N$22 |
| K6:K209 | List | =List!$O$3:$O$13 |
| B6:B22 | List | =List!$C$3:$C$5 |
