Importing columns across two Google Sheets in same spreadsheet

Question

I have one spreadsheet which houses the database of my members. In the same spreadsheet, I have another sheet where I will track attendance for the events for each of these members.

First issue:

Now, I would like to use the names from the first sheet in my second sheet, so as to avoid having to update the names in each of these places. I didn't find any function to accomplish this, so I decided to use the more expensive importrange, by giving the key of same spreadsheet.

The formula is =IMPORTRANGE("15Hr-upZvTXm0c2kAau9G2AQ-7mdqkVkZhItBWqGQYEQ","Database!A:B")

=> Is there any alternative?

Second issue I face is:

Consider I am importing columns A(FirstName) and B(LastName) from a sheet named Database to Columns A and B in a sheet named Attendance. Along with this, in Attendance, there are dates of events in subsequent columns C, D, E, etc.

It looks something like this:

FirstName | LastName | 1stMay15 | 30thApr15 | 29thApr15

Jack      | Smith    | Present  | Absent    | Present
Mark      | Smith    | Absent   | Absent    | Present

And so on...

Now, when I add a new entry in Database for a new member in middle somewhere (because it’s name sorted), thanks to the importRange function, new cells are added right in its place in Attendance (respective to Database), but a new row doesn't get added in Attendance as a result, if I add new member Kyle Smith between Jack and Mark, now Kyle corresponds to the attendance of Mark.

i.e.,

FirstName | LastName | 1stMay15 | 30thApr15 | 29thApr15

Jack      | Smith    | Present  | Absent    | Present
Kyle      | Smith    | Absent   | Absent    | Present
Mark      | Smith    |          |           | 

How can I make it add a new row instead so everyone's attendance stays intact?

Here's the demo spreadsheet: https://docs.google.com/spreadsheets/d/15Hr-upZvTXm0c2kAau9G2AQ-7mdqkVkZhItBWqGQYEQ/edit?usp=sharing

Now, in Database Sheet if you add a new name "Kyle Smith" somewhere amidst other names, THEN in the Attendance sheet, name is added at right place, but rest of data won't move down.

I am open to all kinds of solutions, even using Google Scripts.

Answer 1

The simplest solution is to use a single sheet to register both member information and attendance. You could freeze the name columns and use FILTER, QUERY, Pivot Tables and other functions and tools to do reports and display data in convenient ways.

If you still need or want to use two sheets, I suggest you to avoid to add rows in the middle of the data entry spreadsheets, add new records always at the end. If you are OK with this you could use a third sheet to display the Database records sorted. In the third sheet use SORT function. You could do the same for the Attendance sheet.

A not so simple solution is to use an edit event to evaluate when a row is inserted in Database sheet then find the corresponding row in Attendance sheet and insert a new row there.

References
Freeze or unfreeze columns & rows - Docs Editors Help
Spreadsheet event objects - Event Objects - Apps Script - Google Developers

Answer 2

Use INDIRECT.
For Example =INDIRECT("Database!A:B")