Friday, September 28, 2012

Synchronizing Sheets and Master Sheet option2

Question:

 ( by Tim Welch )

 I have a problem i hope someone can help me solve. I have a master spreadsheet (gradebook) with multiple worksheets that are named with the teacher name. On the master (dashboard) is student data which is automatically added to the teacher spreadsheet with a query function. Now on the teacher worksheet, as the teachers add data, i need that data to flow back to the Master sheet. i have it working with a filter function, however, i have to change the filter for each row to reflect the correct sheet name. I am looking for a way to have a single filter formula in that column that will work to copy down through all the rows. 

Sheet name = t501, t502, t502,...etc.
Filter function (which works and does what i want) is =filter(t501!N$8:BD, (t501!A$8:A)=A9) which correctly pulls all the data from the teacher sheet based on the student ID (in this case=A9).

the problem is to have a formula that can build the sheet name as a variable so it can be copied down a long row of data.

I have partially solved the problem by sorting the students by this teacher code so they are all together, then one can copy down the formula for all the students that have that teacher, but it will be much cleaner to write a formula that will make the filter formula work automatically. I have tried the sheet name as a variable using indirect, and other ideas with no avail.

thanks in advance,

Tim Welch

Solution:

This is the second option for the solution...

Please check also check out the solution by option 1:

Have a look at the screenshot below:

(NOTE: I have changed the sheet name as per the values in Column D)



Put the following formula in Cell N8 and then drag it down:
=filter(indirect(D8 &"!N$8:BD");indirect(D8 & "!A$8:A")=A8)

NOTE: Cell D8 must be same as the sheet name and Cell A8 must contain Student ID, and after inserting it, then drag it down then you will note the formula in Cell N9 will change to:
=filter(indirect(D9 &"!N$8:BD");indirect(D9 & "!A$8:A")=A9)



I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:
http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,


2 comments:

  1. Super Size ShahOctober 12, 2012 at 8:14 AM

    Hi Kishan,

    Again thanks so so much with all of your help but I have yet another issue that I hope you can help me with... actually 2 issues.

    1. If I have a new student come into the school I would add him to that grade master and put the correct ATS code so he gets filtered into the appropriate class. The only issue is that he shows up on the bottom of the class list. I figured that I could sort in the master and it would be fine but it turns out that once in alphabetical order the student shows up in the correct order in their class but they don't have the correct data. The indirect formula does a great job with filtering back the data to the master but if I add or delete a student in the master it messes up everything. Any work around that you know of?
    Thanks in advance for all of your help. Let me know if you need anything else from me.

    Thanks,
    Shetal

    ReplyDelete
  2. Super Size ShahOctober 12, 2012 at 8:34 AM

    So I guess what it really comes down to Kishan is that I want the data for each student ( the data that I entered in that filters into each class and the data the teacher enters that filters back to the master) to stay with the student even if the student si moved to another class.

    ReplyDelete

Subscribe to: Post Comments (Atom)