Share Data Across Two Sheets

Art Schneiderheinze

I have a master sheet with data for a list of courses to be developed in the new term. I am working with a vendor on some of those course. Let's say 10 of the 15 courses.

The sheet also has a column indicating the status of the development. 

I do not want the vendor to edit or view this sheet. Instead, my thought was to create a sheet for the vendor and link to cells for each row in the master sheet that are assigned to that vendor.

 

Let's say BUS 123 and CAP 456 are on the master list. BUS 123 will be assigned to the vendor.

Ideally, I would like all of the information for BUS 123 to appear in the vendor 's sheet and locked. It would have additional columns that the vendor can edit to reflect stages of development. 

The cells are linked because we may be updating courses on the master list, so the vendor should see those updates. 

Then as the vendor updatea on her sheet, we see the status update in the master sheet.

What might be the best strategy to set this up?

Andrée Starå

Hi,

I would probably set this up with cell links per your description. It seems to be the easiest way.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Art Schneiderheinze

I have a master sheet I want no one to access (edit or view). It is a comprehensive list of courses for development in the upcoming terms.

Several of the courses on this list will be assigned to a vendor to develop. Rather than giving her full access to the sheet to edit the status column, I want to create a sheet for her that essentially has much of the same information on my master (i.e., specific columns but only courses assigned to her).

The sheets are linked so if I update any information in the master that is relevant to her, she sees those updates in her sheet. These columns are LOCKED on her sheet though so she cannot edit them.

Her sheet,however, would have some additional columns so she can track progress on the courses. Those are linked back to my master, so I see her updates (or some representation of the updates, like a summary or something). 

Hope this helps.

 

Paul Newcome

You could lock the columns on your master, then include them in a report. As the owner of the sheet, you can edit locked cells with no hassle at all, but when populated on a report, they are un-editable. You can set the report to only pull rows assigned to that one particular person and give them access to that.

Art Schneiderheinze

Can there be columns in the report the vendor can edit (provide status updates) while the other columns (with course information) are locked?

Andrée Starå

Yes, absolutely! 

More info: https://help.smartsheet.com/articles/522077-locking-unlocking-columns-and-rows

Best,

Andrée

Paul Newcome

Yes. Basically... Whatever columns you lock on the sheet will be locked on the report. 

Art Schneiderheinze

The vendor would have to have at least viewer access to the master sheet (if I understand what I've read sbout reports) and any other sheet from which data is pulled to produce the report, right?

My master sheet pulls data from like 5 or 6 other sheets. In order for that data to appear in the report I share with the vendor, would she have to have at least viewer access to all of these, too?

Is there a way ti svoid this, so all i share is the report, which includes mostly locked columns but columns that she can update (which then feeds back into my sheet?