Arrears tracker how to automate it daily

Hi

I am trying to create an automated spreadsheet for tenants who are in arrears. I have to pull off the debt report daily as the tenants billing period are different for each tenants and I have about 12 sites to manage. So I have created an "Aged Debt Report" tab to plug the daily debt report and I have separate tab called "TENANTS" which I need the data to pull to.

I have created the "TENANTS" tab in a table format.

How can I automate this process by:

1) adding my daily debt report into my spreadsheet and automating my "TENANTS" tab - everytime I go to the TENANTS tab and click "=" and link it to my "Aged Debt Report" it does not seem to pull the data through correctly. I need to be able to pull the data directly and automatically update each time

2) how can I get this to flag for overdue to tenants - I have added a formula into my "TENANTS" tab but I want to be able to flag me with a message stating tenant is overdue by 7 or more days. If I can get a how to VBA macro to set up automated emails to be sent from excel/outlook to these tenants who are overdue it would be helpful

3) how can I pull the data for each site and pull the overall debt for each site

I have more stuff I need to be able to do, but I am struggling right now and seem to be doing something wrong.