Update changes made to individual workbooks in the consolidated workbook

[Scrapnforfun]

Hi,
I have multiple individual logbooks (14 in total), a few samples are attached, and a master logbook in which to consolidate the multiple individual logbooks. There is VBA in the master that will consolidate all the individual logbooks into the master and then inserts an "x" automatically in the "processed" column on the individual logbook once consolidated (Thanks to Jaslake).
What the individuals do in their logbook is enter some of the info on a line and then will come back to fill in the rest of that line. If I am to consolidate all the workbooks before the individual updates that line of info, the "x" is already there in the "processed" column and will not look at that line for updates again.
Is there a way to make the master logbook update the new info entered in the logbooks that have already been "processed"? Maybe have the vba code delete the "x" before updating each time?
I don't know. I am so new to this. I can figure out how to change the code when I need to add columns, etc., and that is only because you all have given me the initial code. So I am trying to learn as I go along.
Any help, please!
Thanks so much.
Lin

[MarvinP]

Hi Scrapnforfun,

Excel has a newer tool called Power Query that I think you are looking for. You can install it as an AddIn from the net. It is from Microsoft. Then you run a power query that will pull in all the LogBooks and show them as a single consolidated file.

Search on Power Query and find many examples like:
http://excelunplugged.com/2015/02/10...n-power-query/

[Scrapnforfun]

Hi, Marvin,
The company I work for will not let me download anything from the Internet as it is against policy. I haven't dealt with addins before. Is this something that needs to be downloaded for each user to use it, user-specific download, or can I download it for this specific document and whoever opens the document can use it?
Hope that makes sense.
Thanks for your reply.
Lin

[jaslake]

Hi Lin

Not all records in the Individual Logbooks have a Unique Identifier (eg:BOARD NUMBER) so there would be no way to identify which Record in the Logbook Master to replace.

I can think of only two ways to achieve what you're after:

• Create a Unique Identifier for each individual record in each Individual Logbook. Process the records to the Master. Place an X in the Processed Field.
• Add NEW Records and leave the Processed Field empty. If a Record is modified remove the X in the Processed Field. Run the Consolidate Macro.
• Now, the Consolidate Macro will need to look at each Record in each Individual Logbook that has and empty Processed Field. If it's empty, look in the Master to see if it already exists. If it already exists in the Master replace the Record. If it does NOT exist in the Master add the new record to the Master.
• This approach is very convoluted and I would not recommend it...you'll not be happy with it...it'll get FUBAR very early on.

The second approach would be to recreate the Master each time. This approach is eminently doable if you wish to follow this approach

[MarvinP]

Hi,

The Microsoft Power Query Add-In is from Microsoft and found at:
https://www.microsoft.com/en-us/down...5-fd6dfff39149

If you installed Office 365 or have Excel 2016 you would get it by default. Think of it like another Excel function, like Solver or any of the newer functions. Your IS department shouldn't have a problem with this, unless they are responsible for teaching you how to use it and don't want to learn themselves. The Power Query function is available for Excel 2010, 2013 as a "newer tool" download and default in 2016. It is on the Data Tab in 2016 and called "Get & Transform" instead of "Power Query".

[Scrapnforfun]

Hi Marvin,
I've been looking and trying to use the power query tool but am getting errors. I don't think I am setting it up correctly. Have you had experience with this to point me in the right direction? I think I am getting confused with all the steps it takes and am hoping it is simple compared to what I am making it out to be.
Lin

[Scrapnforfun]

Hi, Marvin,
Forget my last post. I think I figured it out. My only problem now is that the existing workbooks were password protected and from my reading, it looks like the power query doesn't account for that. If you know another way to get around that, to keep the passwords, that would be great. Otherwise, I'm thinking that we should do away with the passwords because this tool works amazing. I think I will have a fight on my hands, though, if I suggest to the office getting rid of the passwords.