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?

  • I'm sorry, questions about specific web sites/services are off-topic here. You might be able to get help at Web Applications. (For more information on our scope, see the help center.)
    – Ben N
    Mar 12, 2017 at 17:39

1 Answer 1


You can achieve this with the following:

  1. Create Sheet 1, containing master data
  2. Create Sheet 2, which users can edit. This draws from Sheet 1 useing VLOOKUP.
  3. 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.

enter image description here

2. Create user edit sheet

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

enter image description here

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:

enter image description here

enter image description here

This will give you a nice dropdown like below:

enter image description here

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

enter image description here

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.

enter image description here

  • Life Saver! Can't say enough. Thank you million times. Mar 28, 2018 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.