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