Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- MacOS
Hello All,
Working on a project and I have no idea how I would achieve something like this so I am hoping someone might have some ideas.
Variables: I have 5 fields of criteria that drive how the sheet will display data
1. My Title
2. A Start Date
3. An End Date
4. A First Drop Count - This essentially tells the user that based on the #2 Start Date how much activity happens
5. Activity Count - Just a total of how much activity there is. This should match the amount of weeks in between the Start and End date (The dates are formatted by week using Friday's Date for each of those weeks)
So, I have mocked up a sample of what the sheet would look like in the event I was able to get this to work.
So, based on the first row of data "Title 1", my start date begins on 11/1/19 and has a First Drop of 3, I would need the corresponding cell in this row to show "1-3", if the First Drop is "1" to display just "1" and since my End Date is 12/20/19 I would need each subsequent cell after the Start Date cell to continue the Counter "2", "3" and so on until it reaches the end date and then one cell to the right of the end date to drop in the title in that cell.
I would most likely need some sort of Validation to look at the start date and end date and get a count of how many weeks are in between and then confirm if the "Activity Count" value matched this number or else flag the user via a msgbox and then exit the macro to allow for a correction.
If any other details are needed please let me know.
Sample
Working on a project and I have no idea how I would achieve something like this so I am hoping someone might have some ideas.
Variables: I have 5 fields of criteria that drive how the sheet will display data
1. My Title
2. A Start Date
3. An End Date
4. A First Drop Count - This essentially tells the user that based on the #2 Start Date how much activity happens
5. Activity Count - Just a total of how much activity there is. This should match the amount of weeks in between the Start and End date (The dates are formatted by week using Friday's Date for each of those weeks)
So, I have mocked up a sample of what the sheet would look like in the event I was able to get this to work.
So, based on the first row of data "Title 1", my start date begins on 11/1/19 and has a First Drop of 3, I would need the corresponding cell in this row to show "1-3", if the First Drop is "1" to display just "1" and since my End Date is 12/20/19 I would need each subsequent cell after the Start Date cell to continue the Counter "2", "3" and so on until it reaches the end date and then one cell to the right of the end date to drop in the title in that cell.
I would most likely need some sort of Validation to look at the start date and end date and get a count of how many weeks are in between and then confirm if the "Activity Count" value matched this number or else flag the user via a msgbox and then exit the macro to allow for a correction.
If any other details are needed please let me know.
Sample
Title | Series Start | Series End | First Drop | Activity Count | 4 | 11 | 18 | 25 | 1 | 8 | 15 | 22 | 29 | 6 | 13 | 20 | 27 | |
Dates >>>> | 10/4/19 | 10/11/19 | 10/18/19 | 10/25/19 | 11/1/19 | 11/8/19 | 11/15/19 | 11/22/19 | 11/29/19 | 12/6/19 | 12/13/19 | 12/20/19 | 12/27/19 | |||||
Title 1 | 11/1/19 | 12/20/19 | 3 | 10 | 1-3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | Title 1 | |||||
Title 2 | 10/25/19 | 12/13/19 | 3 | 10 | 1-3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | Title 2 | |||||
Title 3 | 10/11/19 | 3/13/20 | 1 | 8 | 1 | 4 | 5 | 6 | 7 | 8 | Title 3 | |||||||