How to make my excel report auto update

[LeeWen]

Attached is my current excel report with dashboard. Currently I transfer the numbers manually to tabs in blue to create graphs. I wish to reduce the manual data entry in the graphs' data.

I tried pivot chart, but pivot chart unable to calculate the rate and median for me. I'm using rate and median to plot my graphs.

I want to make the graphs' data can be auto updated when I input the Raw Data and Patient Days SOC tabs.

Please let me know if you can help me. Many thanks.