Above my head

[WCAconsulting]

OK MrExcel geniuses, I need your assistance again as I am beating my head against the wall for the past week trying to get this figured out.

I am helping a non-profit at a church assisting job seekers by providing training, skills development, and support so they can find a job in this tough time. Part of my duties is data tracking.

I have begun a Google Docs (not my choice, but it is what I have to use) sheet for this and I have reached the limits of my abilities regarding finishing it up to function properly and below are the items I am needing assistance with:
1. Need the blue area in the Group tab to calculate average per week for the month and year listed at the top. This would be based on who is assigned to each group on MasterData and MasterNum is where input will reside.
2. The red in the Group tab needs to sum the total for the entire time they have been looking for a job based on data input into MasterNum tab.
3. MasterNum and Worksheet tabs currently sorts MasterData names based on status assigned to only "active" for ease of data entry. I would like to have the MasterNum (or additional if needed) tab filter based on the weeks attendance checkbox to simplify data entry for the week.

Sheet should be open to public editing ability at the link below. If you could post your solution or edit made that would be appreciated so I can learn from it.

Trial #2

docs.google.com

TIA,
Colt

 

[WCAconsulting]

That's because the query function is not linked to the data below it

So the headers will changed on the query, but the data below it, unless it is pulled from a separate sheet, linked to the name, it will not delete the data input manually.

1. Option? you could make masternum independent of the query, pull from the masterdata sheet instead of the worksheet, it shows active & inactive members, and is basically your big fat datasheet, you can hide the columns for inactive members if you want to. We can make it so if the person is inactive their data gets highlighted. Although really this depends on how you're using the worksheet and the masternum files... if masternum will be populated by worksheet, when it can stay as it is, maybe.
2. This is the other option i can think of: you make a helper sheet, like a worksheet that has all the data that you pull into Masternum. But this is a redundant step in the greater scheme of things.

Option 1 is what did and think I will just keep it that way. Little more to sift through, but in the big scheme of thins not much.

If your in the Houston, TX area I owe you a few drinks! Thank you for your help. I will consider this a closed topic.