## 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.

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:

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,