Doug Mutzig
Board Regular
- Joined
- Jan 1, 2019
- Messages
- 57
- Office Version
- 365
- Platform
- Windows
Good evening all,
I have a training table that has columns for Staff name, Rank, unit, company, and then a column for each mandatory training. above the columns I have cells where the end user can enter the duration for training (i.e. 1 yr, 2 yr, .5 yr) which is used by a conditional formatting formula to highlight a training date 30, 60, 90 days before it is due. However, the dates in the cells are not modified (i.e. they could be for last year and due for another year or could be due this year depending on the duration)
What I would like to do is generate a report that lists each staff member and their training(s) that are 30 days or less to due. To add to it additional training's could be added later or current training's removed. I am thinking I need to do something that lists the staff and then run through each of the dates adding the appropriate duration to the date and then compares it to today's date minus 30 days. If the result is <= 30 days then it adds the date to the table. I would also need to pull all of the current column headings to add to the table.
Does anyone have any ideas on this? I tried a pivot table but that didn't work very well for me and I face the issue of not being able to pull in added columns automatically.
Thank you very much for any help you can offer.
I have a training table that has columns for Staff name, Rank, unit, company, and then a column for each mandatory training. above the columns I have cells where the end user can enter the duration for training (i.e. 1 yr, 2 yr, .5 yr) which is used by a conditional formatting formula to highlight a training date 30, 60, 90 days before it is due. However, the dates in the cells are not modified (i.e. they could be for last year and due for another year or could be due this year depending on the duration)
What I would like to do is generate a report that lists each staff member and their training(s) that are 30 days or less to due. To add to it additional training's could be added later or current training's removed. I am thinking I need to do something that lists the staff and then run through each of the dates adding the appropriate duration to the date and then compares it to today's date minus 30 days. If the result is <= 30 days then it adds the date to the table. I would also need to pull all of the current column headings to add to the table.
Does anyone have any ideas on this? I tried a pivot table but that didn't work very well for me and I face the issue of not being able to pull in added columns automatically.
Thank you very much for any help you can offer.
