I have a spreadsheet with columns A-F pulling in data through MS query and
columns G-U having data the is manually entered by a user and is intended to
be "linked" to the data appearing in A-F.

To be more detailed, columns A-F represent customer order information from
our inventory system, while G-U represent payment information. When new
records are added or deleted within the query range on the left, rows ought
to be removed or added (with blanks) in the corresponding spots on the right.

I have tried changing the query properties to:
- insert entire rows for new data, clear unused cells
OR
- overwrite existing cells with new data, clear unused cells
This does not change the data on the righthand side.

Then, I added a bogus column G that contains a useless formula in the hopes
that the "fill down formulas in columns adjacent to data" option would force
data to the right to propogate down with the corresponding data on the left
when the query is refreshed. This doesn't work. Only the formula itself
propogates down.

How can I ensure that the data (entered manually) on the righthand side of
my spreadsheet continues to correspond to the data (from a query) on the
lefthand side when rows are added and/or deleted through the query refresh
process?

Were you ever able to figure out this issue? I'm having the same issue. I work for a school and have 200 kids with data. I used a query to fileter in demographic data and the teachers have enter in their data manually. But if I have to add a new student it messes up all the data on the right side that the teacher entered. Help.. I'm completely at a loss.

Thanks,
Shetal

Hi Shetal,
It is indeed frustrating. I tried several options but couldn't get through. One suggestion that I have is to use a bit of VBA (I can help you with the code if you are happy with the idea). The idea is to run a VBA macro that creates a copy of your existing sheet and then refreshes the data from your query. The new data then refers to the old sheet and applies a lookup function (using a unique ID) to get all the details of existing rows of data. This way you don't have to worry about the position of your data. At the end of it the macro deletes the dummy sheet.

It may sound like a big task but shouldn't take more than 10-15 lines of VBA code.

By the way, if you come across a better idea please let me know.

Thanks
Prashant Kumar Pathak