Importing columns across two Google Sheets in same spreadsheet

Question

I have one spreadsheet which houses the database of my members. In 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 first sheet in my second sheet, so as to avoid having to update the names at each of this place. 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.

=> Is there any alternative?

Second issue I face is:

Consider I am importing columns A(FirstName) and B(LastName) from Sheet1 to Columns A and B in Sheet 2. Along with this, in Sheet 2, 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 Sheet1 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 Sheet2 (respective to Sheet1), but a new row doesn't get added in Sheet2—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.

Catu Catu · Accepted Answer · 2015-05-21T19:24:25

First Issue

I find that import range is a great tool and use it all the time. It can be slow but for normal apps it is fine. For attendance it is definitely fine. I have looked and not found any outstanding alternative at the moment (You could obviously use a script with probably the same speed results).

Second Issue

I cannot directly write the script but you could import the range on a separate sheet in spreadsheet 2 and then when a new row is inserted in sheet2, spreadsheet 2, the script is triggered to search for the position of this new name in sheet 1, spreadsheet 2 and insert a row there. This seems overkill.

If I were you, I would not sort them unless you were exporting the data and needed it in alphabetical order. In that case, in the moment of your export, you could apply a filter and sort the data. This would work. Obviously not your ideal solution though.

Importing columns across two Google Sheets in same spreadsheet

2 Answers