Align the rows of one sheet with the rows of an imported sheet

Question

I have a spreadsheet of employee names. I am using =IMPORTRANGE() to import the names into another spreadsheet that tracks expenses in rows next each of the imported employee names.

The problem is when someone adds another name to the first spreadsheet and sorts it alphabetically, the order of names in the second spreadsheet (imported using =IMPORTRANGE()) changes to reflect the change in the first spreadsheet.

Unfortunately the data in the rows associated with the names in the second spreadsheet does not move as well. These stay static so John's name ends up being associated with Ted's data.

I'd like to be able to 'attach' the other data in the rows in the second spreadsheet to the name so that when the names change row, all data in the associated row move with the name.

Is this possible?

Answer 1

importrange is not going to reorder any rows. It only imports data.

The root cause is poor data structure: the second spreadsheet (with expenses) does not carry any information on whose expenses are those. This should not be acceptable data management: each table must have a column that can be used as an identifier. (Ideally, a numeric Id of an employee, rather than their name, and preferably placed in column A). If you have an identifying column, then data for each person can be looked up using arrayformula(vlookup(...)) combination. Example:

=arrayformula(VLOOKUP(A2:A20, importrange("key", "Sheet1!A1:B30"), 2, false))

This formula imports column B of another spreadsheet, but places data so that the identifying column A matches what is in the column A of the current sheet. This is a robust link between two tables.

---

If you want to keep the current approach, then the best you can do is add a column "date added" for each employee, and agree to always sort by that column*. Then the new employee will always appear at the bottom.

(*) That is, until someone forgets and sorts a sheet alphabetically, resulting in expenses being charged to a wrong person.