I have a workbook with 2 sheets containing the same list of clients, but with different data relating to the client. Both sheets use LastName and FirstName as the first two column headers. Both sheets have about 10 additional columns, each being unique.
Sheet 1 is the "master list" table with 90 current names. We insert a row and add new names as we get new clients. I am trying to get sheet 2, with 65 of the 90 names, to update dynamically when a new row and name is added to Sheet 1.
After researching, I found I could use Power Query to merge Sheet 1 with Sheet 2 and Load the Output to a new Sheet. Let's call it Merged.
It looks great, so I hid the original Sheet 2 because it is now outdated. I no longer want to interact with Sheet 2, I want to interact with the new Merged Sheet. Now when I add a row to Sheet 1, Merged updates nicely with a new row and Names in place.
Problem: I can't add or change data on the Merged Sheet (Query output) without it disappearing as soon as I Refresh. It reverts back to the original.
Can I overwrite/add data to the Power Query output and save those changes? Somehow preventing Sheet 2 from clearing out the new data. Is that even a thing?
Can I Only update one of the connections - Sheet 1 and leave Sheet 2 dormant?
Am I using Power Query when I should be using something like XLookup?
I want to get rid of the original Sheet 2. It has become obsolete now that 30 new clients have been added to Sheet 1, and will continue to fall behind.
Thanks for the help.
Initially I was stumped on how to merge two sheets while shuffling the new names with the old, but a Power Query work for that problem.
Then I had the new table with the data in the correct position and order.
I found the quick tutorial from Jenn at this link; When a row is added to one sheet, automatically add a row to another sheet
This opened my eyes to the possibility that a Query can be only one connection. So I created a new sheet with the single query to the names on Sheet 1.
I copy/pasted the Merged info into the new sheet, and expanded the table range to include the new info and viola! I have a dynamically updating sheet based on the Sheet 1 list of names.
Now I can delete the original Sheet 2 and move forward and the original query.