Saturday, June 23, 2012

yogi_Display Commission Earned By Viewing Team Member Only And Not Other Team Members

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #597   Jun 23, 2012

user senuuar said:
Interesting spreadsheet question
Good Afternoon,
I have an interesting issue I would like your guys' opinion on.  I have a spreadsheet that tracks employees commissions.  I wanted to have a master spreadsheet so I originally created one spreadsheet that, in column A, listed the employee's name and then in columns B onward it listed their position, commissions earned, notes, etc.  Recently, my employees have wanted to be able to see their commissions as the month goes on.  However, I do not want to open them up to see everyone's commissions in the company as that is private information.  My first thought was to create individual spreadsheets, but I have to be able to have a master spreadsheet as well.  Then, I intended on using =importrange to pull the data from my master spreadsheet into the corresponding spreadsheets, filtering based on the employee's name, and then locking the spreadsheet so they could only see their own information and it would update automatically as new entries were made on the master spreadsheet.  Unfortunately, google restricts you to only using 50 importrange functions in a single spreadsheet so this will not work in my situation.
Is there any way you guys can think of to filter these results so each employee can view only their information?
Thank you for your help!
following is a solution to the problem
first let me show an image of the Commission Data spreadsheet

Please note that I add a Team Member Commission Table in the Commission Data spreadsheet. In this illustration, I have also added the Passcode of Team Member in the Commission Table ... but one can do without having to add the Pass code for each transaction by the Team Member.

so without much further ado, here we go ...