Excel Dashboard: Compare Dual Workbooks

I have two Excel workbooks, each with three sheets that hold our medical inventory data. Within these files I track patient counts and the resources they consume. I want a single, interactive dashboard that will instantly show how the same figures differ between the two workbooks, spotlight key variances, and help me spot trends without digging through six separate tabs.

Here’s what I need in practical terms:

• A clean dashboard on a new sheet in one of the files (or in a separate file, if you prefer) that pulls data from all six original sheets.
• Side-by-side views of patient counts and resource usage, with dynamic charts and conditional-format variance indicators.
• Slicers or dropdown filters so I can drill into dates, departments, or any other field already present in my data.
• Formulas or Power Query connections that refresh in one click—no manual copy-pasting.
• Clear labeling so anyone in the team can follow what each number represents.

I’m already comfortable working in Excel 365, so please build with standard functions, PivotTables, Power Query, or Power Pivot—whatever gives the most robust solution without external add-ins. Once it’s complete, a short walkthrough video or written notes explaining your structure will be enough for me to maintain it.

If you’ve created dashboards for comparable inventory or medical datasets before, let me know; seeing a sample will speed up my selection. I’m aiming to get this live quickly, so please share an estimated turnaround time along with your approach.