Different strokes for different folks ... Different data for different sheets ... How #Google Sheets + IMPORTRANGE function solved a BIG PROBLEM for us! #GAFE

Soooo, you know how you have those days that are really crummy and hard to survive?

Wellll...today WASN'T one of those days!  Today was an AMAZING day!  Today, after hours...DAYS...of searching and Tweeting and Googling and testing, I finally solved a solution to a long time problem in our district with the help of GAFE and the IMPORTRANGE function!  YEEHAW!

Here was the problem...

For years our HR Assistant has maintained multiple Excel spreadsheets for multiple departments/groups.  She and the HR Director, of course, have access to the sheet with ALL of the data, including sensitive information like salary.  However, the Technology Department, for example, needs to know who is hired, who is leaving, who is replacing and so forth to collect and issue equipment, but, we don't need to know salaries.  :)

Now, why do we do this?  Because it's what we've always done.  (I know you know that story!)  But with the hiring of a new HR Director comes new questions and new problems and the brainstorming of new solutions.  YEAH!

Here is our solution...

  • We created a new Google Form (in place of our old Personnel Recommendation Form) to be filled out by administrators.
  • The responses sheet from this form is only shared with the HR Assistant and the HR Director.
  • Side note: we set up Autocrat to create and email a printable version of this form to both the Assistant and Director for workflow purposes.
  • In the responses sheet, which really has more info that HR Assistant needs for her workflow, I created a new tab (sheet) for only the info she needs.  I used a function that looks something like this to pull data from the Form Responses tab.   ='Form Responses 1'!AF3
  • Then, I created a totally separate sheet for the Technology Department and used the IMPORTRANGE function to pull ONLY CERTAIN DATA from the HR sheet.  
Now, are we the first people to ever think of this?!  Nope.  Are there 100 other ways to probably do it?!  Yep.  Do I care?!  Nope.  :)  I think this is going to work for us with minimal work and minimal training, which is a win win in my book!

The short video below will give you a visual overview of the process, which probably needs to be tweaked a little and fine-tuned, but I definitely think we're on to something.  :)  All in all ... not a bad day!  If you would like help setting this up or are doing something similar, I'd love to hear from you!