#1
|
|||
|
|||
Adding notes to a query data table
I have a data table that is queried from a sharepoint database of lessons learned. A column exists in the database that indicates whether a lesson requires corrective action or not. I want to be able to filter the data to show only the "actionable" lessons and then take notes on exactly what action needs to be taken and progress on it.
I have tried adding a column, pulling the data into a new tab, and a few other things but everytime I refresh the data the notes do not stay with the right row. They're not linked in any way. Is there a way to make sure the notes stay with the right line item? |
#2
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding default text to notes with VBA | trevorc | Excel Programming | 2 | 11-20-2018 01:52 AM |
Extracting Data From the Internet Using New Web Query Feature | Rod_Bowyer | Excel Programming | 2 | 04-01-2016 01:51 PM |
How to populate cells in Sheet2 with Data Source query using cell data from Sheet1 | bobznkazoo | Excel | 2 | 03-27-2014 11:14 AM |
Data query import | Gandalf | Excel | 9 | 02-12-2013 03:03 AM |
How to Query Text in MS-Excel 2010 or Import Data & Query outside of Excel? | mag | Excel | 0 | 10-18-2012 11:15 AM |