Excel filters with live and non-live data

Hi,

I have created a spreadsheet for our attendance tracking. The spreadsheet contains some live student and attendance data pulled from our MIS, alongside non-live columns containing weekly comments and attendance categories (excellent, at risk, etc.). This non-live data is not held within our MIS so cannot be part of the liva data feed.

I need to be able to either filter or sort the data by attendance category. When I try to do this when highlighting the entire dataset, the functions are not available. This, I presume, is because the live and non-live data columns are acting as two separate entities. I can filter the attendance category but this doesn't also sort the live student data (so the attendance category is not related to the student data), or vice versa. I can extract the data as values into a different worksheet then filter, but this is time consuming and needs to be re-extracted each time the data feed is updated. 

Is there a way to filter from the original live and non-live data together (i.e. I could filter by absence category and this will also filter the live student data in the related rows)?

(We do have PowerBi but we need to be able to have this in Excel to add weekly comments relating to attendance). 

Thank you in advance!