Hello. I have two sheets of information that I would like to be mirrored over. There is one particular set of information that I need on both sheets, but would hate to have to copy and paste each time I add a new row for both sheets (Names and Dates). The way each sheet is organized is that I have Names and Dates in different columns, each name and date being in a different row on that column, both being paired (lets say Column A Row 1 has the name Jeff, and Column B Row 1 has the date Jeff's joined the session).
These two sheets need to have these basic sets of information - as I have already added the =importrange(...) function - however, there needs to be other separate information on each sheet.
For example, Sheet1 needs to have the basic Names and Date, but also needs to have a certain persons attendance on different columns in the same row (say, Column C of row 1 - Jeffs row and starting column of attendance - needs to have the letter "A" for attended, and that pattern continues to Column Z); Sheet2 needs to have the imported Names and Date from Sheet1, but it needs to have various information on different columns, such as if this task was completed, whether they have a certain file on record, or if they have a LOA.
I have over 100 different names in each row, and it would be a pain to have to copy and paste each new member into a sheet that needs to be symmetrical and has to have the exact same information. It is also great to have these two pieces of information (attendance and other forms) in separate sheets, as I am able pinpoint what exactly I am looking for, and it removes the need to have attendance and other information in the same sheet without looking silly or a pain to have to drag through. Ideally, I would be able to have more sheets of other separate information in the future.
The problem I am having is that, whenever I move a row on Sheet1 to a different position (lets say I am organizing it alphabetically, and I add a new person into the list), although it updates in Sheet2 through the importrange function, the information on Sheet2, such as the notes or LOA requirements, do not update to the row change, and remain in the same spot; ergo, the information from Sheet2 does not accurately show which person it was matched up with originally.
For example, in Sheet1 (where the information for Name and Dates are originally placed) if I moved Row 1 (Jeff), past Row 2 (Amy), and between Row 3 and 4 (Marcy and Sue, respectively), this makes Jeff's Row 3, Amy's Row 1, Marcy's Row 2, and Sue's Row the same (4). Because the attendance is originally placed in Sheet1, this works just fine. However, although the information for Names and Dates do update in Sheet2, the information like the tasks or LOAs do not update. Suddenly, Jeff is on an LOA he wasn't on before, and Marcy is then punished for not filing out her LOA, who is now on Amy's original row (2), which is not good!
I have tried to use other functions, but they do not produce the same result of having the exact positioning of the rows (Names and Dates) as the importrange function does. However, I am unaware of any other functions that could produce a better or similar result that could fix this problem with the rows of different information. If there is any idea of how to fix this, I would be very grateful of other functions or help with a different system that provides an easier solution.
(I would like to note this is done in Google Sheets -- is it better to use Excel or is there an Excel equivalent?)
Bookmarks