View Single Post
 
Old 07-17-2021, 10:21 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 873
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

1. Add another sheet (ID sheet), and make an additional query, which returns a list of unique identifiers for lessons (lesson ID in case corrections apply to specific lessons, a calculated combination of lesson id and person ID in case corrections apply to specific lesson for specific person);
2. In case identifiers on added (ID) sheet aren't present in your main query, add a matching calculated field to main query;
3. Create a named range based on unique ID on added (ID) sheet;
4. Add another sheet (Notes sheet) and create a 2-column table there with 1st column for unique ID, and another for notes. For ID column datarange in Notes sheet, create Data validation List with created Named range as source;
5. On Notes sheet, select an ID from Data Validation list, and add any notes for it you need to. Fill the table and register all wanted notes in this way;
6. Create a calculated column as rightmost column of original querytable, which returns notes from Notes table (e.g. using VLOOKUP based on {added} unique ID in querytable). Edit parameters of original query in a way, that calculations in querytable are updated automatically;
7. Hide ID sheet (look p. 4);
8. Use column which indicates corrective actions to set an autofilter for querytable of original query in case you want only records with must have actions registered.

NB! You cant register any additional info directly into querytable, as with high probability it will be misplaced whenever the query will be refreshed. You have to register such additional info into separate table, and use worksheet formulas to add this into querytable. And you must have a field in querytable which unequivocally determines, from which row of additional table the info must be read.
Reply With Quote