I have seven Excel files containing information about admissions of several hundred patients to various hospital departments, and data appertaining to these (length of stay, type of discharge etc.) The first column always contains the patient ID, the following one contains the dates of admissions. These are usually many dozens per patient, so each patient has dozens to sometimes hundreds of rows (each row containing the patient ID in the first column).
For my research I only need the data from a 2-year period after they completed a certain treatment, however this index date is different for each patient. So I can't just have all rows in the spreadsheet deleted that fall outside the period between two universal dates, rather this period differs for each patient.
I was wondering whether there is a way that, if I set up one spreadsheet with the patient IDs in the first column and the start and end date of the follow-up period in the second and third column, I could link this to my other spreadsheets (or put it all on separate sheets in the same file) and have Excel automatically delete all rows in the other sheets that fall outside this period for the specific patient?