Forum Discussion
Trying to pull specific data from spread sheet to a calendar
Hello,
I'm trying to figure out an easier way to populate specific data totals from a spread sheet to a calendar. i have made a spread sheet similar to what we use (excluding patient data of course). i have to keep track of the type of beds we are needing for each day, i made a calendar to help me at a glance to see the totals but now i am unsure of what formula i could use to total the bed types for each day. I'm sure it is an easy thing but my googling has failed me.
here is the calendar.
sneakysloth Consider using a pivot table to skip the use of potentially complicated formulas. The attached file contains a small example of what's possible. Note that the data table is formatted as a structured Excel table and that there are no empty rows separating the dates.
Then the pivot table summarizes the data by day and bed type. Exactly what you need but not in a typical (paper) calendar format.
Riny_van_Eekelen this is perfect, i actually haven't used pivot table before so this was a cool tool I'm going to have to experiment with more in other applications. Thank you!
Hi sneakysloth
I would recommend you follow the easy route suggested by Riny_van_Eekelen
A calendar sounds doable with Power Query (+ Excel 2021 or 365):
If no Period is selected in the drop-down list current Month + Year is used by the query
(not intensively tested but appears to work decently...)
If you use a pivot table, be warned: Pivot tables use a cache to hold summary data from their source. If you update the source data, you need to remember to Refresh the table (cache) to see accurate results.
But a calendar does not have that limitation, and it need not use complicated formulas. See the attached workbook, and read the notes on the _Info worksheet. Let me know if you have questions.
I think you can try this out just by copying and pasting your bed assignment data (only the Date and Bed columns are needed!) over the sample data on the Assignments worksheet.
