Suppose you have a private spreadsheet which contains sensitive payroll and annual leave information, but you’d like to publish some of the information to staff members for their quick reference. How can you do this with EditGrid’s new feature, linked sheet?

(It really is private — if you’d like to verify, check it out here.)

It’s easy to create a linked sheet. First, on the source, private spreadsheet, create a new sheet, say, “Annual Leave Report”, that contains only the publishable data, but no more.

Then, create a new spreadsheet and grant it the necessary permission for staff members to view. Next, insert a new sheet on this to-be-linked spreadsheet — as usual, you may either make use of the (+) button at the sheet navigation bar, or use the sheet context menu as illustrated below.

Now you can see that there’s a new button called “Link from another spreadsheet”. Click it to expand the toolbox.

Select the right source, that is, in our case, my spreadsheet “IBD Payroll” where we can find the sheet “Annual Leave Report”. The new sheet name is automatically detected. You may also give it a name, say, “IBD Payroll#Annual Leave Report”, later on for easier identification. Click [OK] to confirm.

Bravo! The linked sheet is successfully created. Note that the sheet is copied from our private source at once. Note also the “chain” icon in the sheet tab. Now all users with read permission on this linked spreadsheet, “IBD Annual Leave”, can see the publishable data from the private source. You may even build additional reports based on the source data using ordinary cross-sheet formulae:

Now if you have any updates to the payroll and annual leave data, you don’t need to copy the updates into two spreadsheets: just update them on the source spreadsheet, and users of the linked spreadsheet can retrieve the most up-to-date data from the source spreadsheet via the linked sheet using the Update Linked Sheet function accessible at the sheet context menu.

Don’t repeat yourself, any more!

Resources:

• The private source (really private!)
• The linked spreadsheet

P.S. Only a user with read permission to the source spreadsheet and write permission to the linked spreadsheet can create the link sheet, and afterwards all users with read permission to the linekd spreadsheet can update it. Therefore, you won’t be able to retrieve the sensitive data on the “Staff Record” sheet. Try it — to unlock the linked spreadsheet to gain write permission, the password is IBD.

Del.icio.us | Digg | Stumble it! | reddit | Facebook

This entry was posted on Tuesday, November 28th, 2006 at 4:04 am and is filed under Cookbook. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.