Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Generate data on 1 worksheet from 2 others (2007/SP1)

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Generate data on 1 worksheet from 2 others (2007/SP1)

    Is there any chance that you could use Access for this? It would be more suitable for this problem.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generate data on 1 worksheet from 2 others (2007/SP1)

    Yes, I can use Access. I'll try that. Thanks for the suggestion. I suspect I'll visit you again in the Access forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •