Google Sheets – Summoning Data from Another Sheet

google sheets

I want to build a MASTER sheet that saves NON CHANGEABLE data like the following:

Course ID - Course Units - Course Title - CRN - ACCT

these data will be permanent and non changeable no matter what.

However I need to create another table that has a data entry interface so when the user enters the Course ID it will summon the other data and insert it,
then the user will fill other changeable data, like course time, date, etc.

Any idea how to do that?

Best Answer

You can achieve this with the following:

Create Sheet 1, containing master data
Create Sheet 2, which users can edit. This draws from Sheet 1 useing VLOOKUP.
Lock Sheet 1, so only Sheet 2 can be edited by users.

Caveat: Note that Sheet 1 will be locked, but still visible to users.

1. Create master data sheet

In Sheet 1, enter master data like below.

2. Create user edit sheet

In Sheet 2, create headings for data you want to display.

Still in Sheet 2, select the cell in which you want the user to toggle their choice. Make this a dropdown field based on your master data:

This will give you a nice dropdown like below:

Then, use VLOOKUP to get the relevant values from Sheet 1 to display on Sheet 2. I've also used IFERROR so that when the selection in B4 is blank, B5 is also blank (rather than displaying an error).

3. Protect master data sheet

Lastly, use Data > Protected Sheets and Ranges to restrict which sheets or ranges users are able to edit.

Caveat: Note that Sheet 1 will be locked, but still visible to users. Perhaps using "hide rows" in Sheet 1 (as well as protecting the sheet) will do the trick, but I haven't been able to test this idea.

Related Solutions

Google Forms – Report Only the Last Entry Made by User

One approach is to add a status column next to the data input: for example, in L10 it would be

=if(iserror(match(H10, H11:H, 0)), "current", "obsolete")

which returns "current" if no entry below the present row has the same email. Then your query can filter out the obsolete results.

The above does not work with arrayformula. I don't think there is an arrayformula-compatible approach, because the number of comparisons is quadratic in the size of array. Here is a script solution that auto-expands: put status(H10:H) in a new column, with the following function in a script.

function status(arr) {
  flat = arr.reduce(function(a, b) { return a.concat(b); });
  var output = [];
  for (var i=0; i<flat.length; i++) {
    if (flat[i] === '') {
      output.push(['']);
    }
    else if (flat.slice(i+1).indexOf(flat[i]) === -1) {
      output.push(['current']);
    }
    else {
      output.push(['obsolete']);
    }
  }
  return output;
}

The approach I took in this answer also works reasonably well here: use unique to get unique users (identified by emails), then filter rows by email, and use vlookup to pick the data just from the last row. The result will be a neat summary table that has only the latest data.

Using your example, I build such a table starting from the cell A20. In A20, I enter

=unique(H10:H)

obtaining the list of emails. Then in B20 I enter

=vlookup(999999, filter($G$10:$K, $H$10:$H=$A20), column()+1)

and extend this formula to the range B20:D. This is the result:

+-----------------------------+-----------------+--------+-----------------+
| peter.goedtkindt@school.edu | SpecializationB | 2016.2 | changed my mind |
| student1@school.edu         | SpecializationB | 2016.1 |                 |
| studnent2@school.edu        | SpecializationC | 2016.1 |                 |
+-----------------------------+-----------------+--------+-----------------+

(I don't know if you needed comments, but I included them anyway.) Using column()+1 as the last parameter of vlookup allows the formula to pick the relevant column, after vlookup with absurdly large search key 999999 zooms in on the last entry.

Google-sheets – Auto entering functions in sheets managed by forms

I have a sheet-bound script to generate and email templated documents populated with form response data. What you'll see below is definitely NOT pretty/efficient/possibly even advisable..? However, it does accomplish what you are attempting in this post.

It's more or less a sledgehammer grade function to pin a percentage on each submission based on yes/no/blank values for a variety of checksheet-style questions.

The trick - I think - to getting it in the appropriate row and not overwriting data is the placement. The following lines:

var calc = '=round(if(iserror(countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")/(countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")+countifs(INDIRECT("N"&row()&":BA"&row()),"=NO"))),"",countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")/(countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")+countifs(INDIRECT("N"&row()&":BA"&row()),"=NO")))*100,1) & "%"';

var calcc = calc.toString();

var sheet = SpreadsheetApp.getActiveSheet();

var numRo = sheet.getLastRow()
sheet.getRange(numRo, 1).setValue(calcc);

precede the main function:

function onEdit() {... etc

which is triggered on a form submit.