Peter Muller
Board Regular
- Joined
- Oct 15, 2018
- Messages
- 133
- Office Version
- 365
- Platform
- Windows
Hello Experts,
I desperately need help to finish a task I am busy with, which stems from my previous post.
I require VBA coding for the following:
Column’s B-E is populated daily, below the last entry the previous day. The # of entries for the day will vary, dependent on certain criteria. The last entry for the day will always be “Summary”, which is pasted into Column D.
In my example below, Row 9 is the sum row for the previous day, and rows 10-14 are the pasted rows for the new day.
The formula in Col F should thus read:
I desperately need help to finish a task I am busy with, which stems from my previous post.
I require VBA coding for the following:
Column’s B-E is populated daily, below the last entry the previous day. The # of entries for the day will vary, dependent on certain criteria. The last entry for the day will always be “Summary”, which is pasted into Column D.
In my example below, Row 9 is the sum row for the previous day, and rows 10-14 are the pasted rows for the new day.
The formula in Col F should thus read:
Previous Day F9) =IF($G9="Yes",H9,IF(G9="No",0,IF(G9="Sum",SUM(F$6:F9)/SUM(H$6:H9)))) New Day |
F10) =IF($G10="Yes",H10,IF(G10="No",0,IF(G10="Sum",SUM(F$10:F10)/SUM(H$10:H10)))) |
F11) =IF($G11="Yes",H11,IF(G11="No",0,IF(G11="Sum",SUM(F$10:F11)/SUM(H$10:H11)))) |
F12) =IF($G12="Yes",H12,IF(G12="No",0,IF(G12="Sum",SUM(F$10:F12)/SUM(H$10:H12)))) |
F13) =IF($G13="Yes",H13,IF(G13="No",0,IF(G13="Sum",SUM(F$10:F13)/SUM(H$10:H13)))) |
F14) =IF($G14="Yes",H14,IF(G14="No",0,IF(G14="Sum",SUM(F$10:F14)/SUM(H$10:H14)))) |
B | C | D | E | F | G | H | |||
Project | Category | Description | Date | score | Completed | Weight | Details/Formula in Col F | ||
9 | PM | Score | Summary | Fri 22/05 | 76% | =IF($G9="Yes",H9,IF(G9="No",0,IF(G9="Sum",SUM(F$6:F9)/SUM(H$6:H9)))) | |||
10 | Finance | Petty Cash | Reconcilliation | Mon 25/05 | 5 | Yes | 5 | =IF($G10="Yes",H10,IF(G10="No",0,IF(G10="Sum",SUM(F$10:F10)/SUM(H$10:H10)))) | |
11 | Finance | Petty Cash | Replenish cash | Mon 25/05 | 5 | Yes | 5 | =IF($G11="Yes",H11,IF(G11="No",0,IF(G11="Sum",SUM(F$10:F11)/SUM(H$10:H11)))) | |
12 | HR | T&A | Registers monitored | Mon 25/05 | 0 | No | 2 | =IF($G12="Yes",H12,IF(G12="No",0,IF(G12="Sum",SUM(F$10:F12)/SUM(H$10:H12)))) | |
13 | HR | T&A | Late & Absent actioned | Mon 25/05 | FALSE | 2 | =IF($G13="Yes",H13,IF(G13="No",0,IF(G13="Sum",SUM(F$10:F13)/SUM(H$10:H13)))) | ||
14 | PM | Score | Summary | Mon 25/05 | 0 | Sum | =IF($G14="Yes",H14,IF(G14="No",0,IF(G14="Sum",SUM(F$10:F14)/SUM(H$10:H14)))) |