I've been stuck on this for quite a while and I don't think this can be done without VBA of some sort, which I am terrible at. Hope someone can offer some advice!
On the first worksheet, I have the below table. Column B, is a drop-down with the 4 options displayed.
On a second worksheet, I have something similar to the below but for the full year
I'm trying to break the costs down from the first table by calendar week in the second table, depending on the COST INTERVAL option selected in the dropdown. I would like the cost intervals to do the following:
OneOff will copy the Total Cost to the corresponding week in the second table according to COST WEEK
MultiWeek will take the Total Cost, divide it by the DURATION in Column D, and put the result in the corresponding weeks in the second table (in this case, weeks 3 to 9)
Recurring will take the Total Cost, divide it by the DURATION in Column D, and put the result in the corresponding weeks in the second table (in this case, Weeks 1, 6 and 11. 3 times (column D) in 5 weeks (Column E))
UserDefine will change the current worksheet to the second and the user will manually go to the corresponding week and enter the data in
OneOff I can do with a lookup or index& match forumula. Multiweek and Recurring I'm messing around with formulas and think I might be able to get it although it's beginning to look quite convoluted.
My probelm is the UserDefine option. I think this needs VBA to go to that worksheet and the other problem is that if the user manually enters a value, it will erase any formula in the cell.
Does anyone know of a better way to do this or how I can go about learning the VBA required to do this neatly?
On the first worksheet, I have the below table. Column B, is a drop-down with the 4 options displayed.
| COST ITEM | COST INTERVAL | COST WEEK (FIRST) | DURATION | FREQUENCY | TOTAL COST |
| A | OneOff | 6 | - | - | 50 |
| B | MultiWeek | 3 | 6 | - | 600 |
| C | Recurring | 1 | 3 | 24 | 200 |
| D | UserDefine | 7 | - | - | 10 |
On a second worksheet, I have something similar to the below but for the full year
| CW | 1 | 2 | 3 | 4 | 5 | 6 | 7 | |
| Cost Item | ||||||||
| A | ||||||||
| B | ||||||||
| C | ||||||||
| D | ||||||||
I'm trying to break the costs down from the first table by calendar week in the second table, depending on the COST INTERVAL option selected in the dropdown. I would like the cost intervals to do the following:
OneOff will copy the Total Cost to the corresponding week in the second table according to COST WEEK
MultiWeek will take the Total Cost, divide it by the DURATION in Column D, and put the result in the corresponding weeks in the second table (in this case, weeks 3 to 9)
Recurring will take the Total Cost, divide it by the DURATION in Column D, and put the result in the corresponding weeks in the second table (in this case, Weeks 1, 6 and 11. 3 times (column D) in 5 weeks (Column E))
UserDefine will change the current worksheet to the second and the user will manually go to the corresponding week and enter the data in
OneOff I can do with a lookup or index& match forumula. Multiweek and Recurring I'm messing around with formulas and think I might be able to get it although it's beginning to look quite convoluted.
My probelm is the UserDefine option. I think this needs VBA to go to that worksheet and the other problem is that if the user manually enters a value, it will erase any formula in the cell.
Does anyone know of a better way to do this or how I can go about learning the VBA required to do this neatly?
