Forum Discussion
Auto Generate a return value based upon criteria in schedule
Hello,
I am trying to return a value (employee) as many times as the criteria (task scheduled) has populated in excel. However, based upon how many times the employee is assigned the task there are expectations on how many should be completed. For example if Julia is scheduled for coachings Monday, Wednesday Thursday & Friday based upon a 3 block day, she is expected to complete 24 coachings. Therefore, I would need Julie to auto populate 24 times to assign her 24 coachings on a different sheet. Each block is considered for 2 coachings.
- Hi Kristan,
Would you be able to upload the excel file (via OneDrive) so that others can check and provide proper guidance?- I uploaded it!
Hi Kristan_Bass,
I would give the below suggestions:
- Use Macro to generate only the required rows for each trainer based on the criteria, OR
- Create fixed rows for all the trainers and then filter out the rows which are not applicable (Holiday, Leave etc).
1st option would be helpful if the Coaching sheet is like a report which is not updated separately and is only derived from the allocation sheet (08.20.23).
2nd option will be helpful if the Coaching sheet has formulas to be used after the data received from allocation sheet.
Generating the variable no of rows might be possible with the newer formulas in Office 365, but it might become tedious if you have to do it manually for each trainer, every week. This is where writing a VBA MACRO once and using it for repeatedly with 1 click would be helpful.
That's my thought, not sure if someone has any better suggestion than this.
- yes!
