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.
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
=> 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.
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
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.