Attached is an example of possible solution using worksheet functions only.
Columns in Tables with differently colored headers are helper columns, and probably you prefer to hide them from users;
I had to format datetimes in data table differently, and to edit some dates, as with my regional settings Excel read them as strings, or switched months and days. Probably you have to reformat those columns in your format (and check dates);
As in helper column of Report (DataRow) are present row numbers of matching rows of data Table, I advice to use INDEX() instead of e.g. VLOOKUP() to read all info from data Table - I think it will be somewhat faster;
User can not enter any info directly into Report Table, because whenever user sorts the data Table in different order, all Report data row positions probably will change, but any manually entered information remains in old position - i.e. will be attached to wrong row. So either you remove columns Progress and Feedback, you enter this info into data Table, or you will have a separate Table, where you enter this info with matching ID, and report reads it from there;
You can't have such reports without having at least some empty rows at bottom (the safest way will be having at least as much rows for Report as there is in data Table). You can always set an autofilter e.g. for ID column being not empty (don't forget to refresh the filter whenever some data are added);
In case having empty rows in Report will be a problem, you can use ODBC query from data Table instead of using worksheet formulas (using StartAge and EndAge fields as query parameters). You can set the query to be refreshed automatically whenever any parameter field is edited, and whenever the workbook is opened. And you also can refresh the query manually whenever you need it.
|