Share via

How to create dynamic worksheets that show scheduled volunteers by date based on master schedule

Marianne McNamara 0 Reputation points
2026-06-05T00:36:01.7333333+00:00

Hello, I have been searching for a solution to this problem unsuccessfully for hours. I work with a non-profit organization that posts the schedule of our 100 volunteers across 60 dates. I would like to create additional worksheets that provide the schedule BY DATE which should be easy enough to do because each scheduled volunteer is indicated on the master schedule/worksheet by a '1' in the cell. I have been copying and pasting and deleting from the Master to produce each of the 60 dates, but this is extremely time consuming and is also not DYNAMIC in that any changes to the master will not be reflected in my created worksheets. I feel that there should be a way to copy and paste the entire worksheet using an 'IF' function, but I just can't get it right and I am not familiar unfortunately with VBA. Anyone aware of a simple function that would only paste individuals scheduled for a specific date AND that will update when changes to that master schedule/worksheet are made? Thank you.

Microsoft 365 and Office | Excel | For business | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vivian-HT 16,595 Reputation points Microsoft External Staff Moderator
    2026-06-05T02:28:13.48+00:00

    Dear @Marianne McNamara I completely understand the frustration that copying and maintaining 60 separate sheets manually is very time‑consuming.Since your master schedule uses a “1” to indicate that a volunteer is assigned to a specific date, you can use the FILTER function to automatically return only those volunteers for any given date. This function is designed to extract only the rows that meet a condition and will update automatically whenever your source data changes.

    For more information, you can refer to this article: Excel FILTER Function: Extract Data Dynamically with Examples

    Disclaimer: This link is shared by community members for your convenience. It points to a third-party site that is not managed or verified by Microsoft. We can’t guarantee the quality, safety, or suitability of any content or software found there. Please review carefully and make sure you understand any potential risks before using it.

    • For example, on your “by date” worksheet, you can use a formula like this: =FILTER(Sheet1!A2:A101,Sheet1!B2:B101=1,"No volunteers").
    • Once you enter this formula, Excel will automatically return only the names where the value is 1 for that date, and it will stay fully in sync with your primary sheet, no more manual copying or updating required.

    User's image

    If you prefer not to use formulas, another alternative is to use Excel’s “Paste Link” option. This allows you to link cells from your master worksheet to another worksheet so that changes in the master are reflected automatically.

    Go to the destination cell where you want the link > Right-click > In the Paste Options dialog, click Paste Link.

    User's image I hope the information shared helps point you in the right direction. If I misunderstand anything, please feel free to correct. If possible, could you share your example excel screenshots to me for better understanding?

    The first response may not always resolve the issue right away, but with your help and more details, we can work toward a solution.

    Thank you for your patience and understanding. I'm looking forward for your reply.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.