How to provide manual data entries linked to dynamically updating data? unsolved

[LeroyJenkins608]

I have a simple spreadsheet I'm using to track work with multiple colleagues. The idea is, one comes in, updates columns B-D with their notes to track their work and provide status updates.

Column A is populated from an online JSON web request. I have that part working great, every time the file opens it populates column A with ~100 or so entries based on data from an external web page.

The problem I'm trying to solve is how to have the manual updates in columns B-D match their corresponding rows in column A. For example, If I make an update in B2 based on the value in A2, and the column A changes on the next Refresh so that the value in A2 is now in A3, the corresponding manually-entered data in B2 needs to move to B3. Or, if A2 is deleted, B2 should be deleted.

I've been looking at Pivot Tables, but the idea of manually entered data kind of defeats the purpose of a Pivot Table.

Maybe its possible that Excel isn't the right solution for this type of workflow? Thoughts?

[#2]

@LeroyJenkins608 

the solution can be achieved in two ways - 

1. Restructure the excel work book.  This will need two sheets to keep things clean.  Sheet 1 will have JSON web request , which gets refreshed dynamically.  Columns B-D will come from sheet 2. You can use VLOOKUP formula to do this.  Sheet 2 -  for your colleagues to enter data.  Column A remains to be JSON web request in sheet 2, but it will be static.

2. Use a Macro to take care of the mapping, remapping , deleting rows when data gets refreshed. this may be a bit complex solution to maintain, but you retain the structure of your workbook.