Ok,
I'm guessing my original explanation was a touch poor.
I am import ranging maybe 8 columns from a central database into numerous other spreadsheets in use around the school.
As new students arrive they will enter the central database in alphabetical order and therefore appear around the school in that new order. However the existing data which will be input by staff needs to be altered to take into account the new additions. Otherwise a new student will be added into the mix and automatically 'pick up' another students marks, thus 'pushing' the bottom student so no marks are assigned to him.
I found this will googling, although I can't fully understand what it's suggesting. I have a unique figure in a column which could be useful...
Ok, what you are explaining now is different from the way I was understanding it in my previous comment.
What you are trying to do will not work as expected. The reason being, the data been imported with the importrange() function from the source spreadsheet is dynamic data. The data you are entering into the additional columns to the right of the imported data is static data. You cannot lock static and dynamic data together.
The additional columns that you are adding to the right of the imported data do not know that it belongs to the data being imported. So the imported data dynamically changes and cause the data to shift. The static data will stay put as you entered it, and this is the misalignment in data that you are experiencing.
Just to be clear, this is not a bug. It's just the way spreadsheets work.
Therefore, a different approach is needed to overcome this issue.
It all comes down to spreadsheet design. And this also depends on the scenario as not every situation will work.
The only way you'll have success with something like this is to create an additional tab in your destination spreadsheet. This is where you will select items from the imported data via Data validation which will also become static data. You would then use the vlookup() function to pull the remaining data from the imported tab into this new tab to complete each row. Then you'll be able to enter the additional data in columns to the right of that. The result being, all the data in the new table will now be static. This new data table will not be affected by the data being imported as new rows are being added or deleted in the source spreadsheet.
So depending on the data that you have, you will need to determine if you have a column with unique data that can be used as a key. This key will be used in the Data validation drop-down. You would select the key from the Data validation drop-down in the first column, and then fetch the additional data for that row from the imported data tab using the vlookup() function, to populate the remainder of that row. Then the information that is being entered in the same row in the columns to the right of that will remain intact. And this is what you are trying to achieve.
If you feel this is a method that would work for you, and you find that you do not have a column with unique keys in it, I would recommend that you do add an additional column in your source data for this purpose. In general, this column would be the first column in the table.
Unfortunately, you have to break away from total automation. You will need to create this new table to make this work. It is not the ideal situation but is doable.
