Attached is a workbook containing three worksheets:
ExpectedVisits
PlannedEnrollment
StudySchedule
Could people please suggest an approach to ‘automating’ the ExpectedVisits sheet?
Here is the scenario: Imagine three clinical studies in which patients are enrolled. Each study has a schedule of clinic visits (StudySchedule). The researchers expect a certain number of patients to enroll over the next few months (PlannedEnrollment).
The data on the ExpectedVisits worksheet, therefore, depends on the conditions set in the other two worksheets. For planning purposes, the enrollment date for each expected patient is set at the 15th of the month. Patient IDs are assigned arbitrarily.
I’ve manually created sample data for the whole workbook to show the output I’d like to see. In reality, data in PlannedEnrollment and StudySchedule would be entered manually, BUT I would like the ExpectedVisits worksheet to generate itself automatically from the data entered in the other two worksheets, and to update itself to reflect changes in the other two worksheets. Various data could change — number of patients expected, schedule of visits, etc. So the ExpectedVisits worksheet must “know” how many patients are expected in which study in which month, and respond with the appropriate number of rows.
Being very bad with VBA I would love it if this could be done with formulas only, but if VBA is needed then so be it. I’m no programmer but suspect I’ll need to think like one to get this done. All advice would be very much appreciated.
Thanks in advance,
Erik
