Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-12-2021, 02:08 PM
mazloum91 mazloum91 is offline Adding notes to a query data table Windows 10 Adding notes to a query data table Office 2016
Novice
Adding notes to a query data table
 
Join Date: Jun 2021
Posts: 3
mazloum91 is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 07-17-2021, 10:21 AM
ArviLaanemets ArviLaanemets is offline Adding notes to a query data table Windows 8 Adding notes to a query data table Office 2016
Expert
 
Join Date: May 2017
Posts: 869
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
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding notes to a query data table 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
Adding notes to a query data table 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft