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

 

[p_s]

I've tried something, let me know if it is ok. I've copies the sheets I was editting and put a p_s infront of it.

Other details about my edits in comments of the cells

 

[WCAconsulting]

p_s, sorry for the delay in response. I will look at it today and I appreciate the help.

 

[WCAconsulting]

*p_s thank you for this! I believe this will work. I appreciate the comments so I can learn and nice catch on the Tuesday date, our meetings occur every Monday.
On the lifetime interviews, you are correct that it is slightly redundant however it does need to calculate the sum for an individual based on the weekly "interview" input.
I did have one question: on the averages sheet it looks like you used 7 to calculate the weeks per month, is that correct? Will it accommodate a 5 Monday month?

 

[p_s]

@WCAconsulting, I have added helper columns to my copy of your MasterNum file, what I am doing is counting every time the MONTH no. occurs in that column using the year category. divided by 7 because there are 7 times it appears each week (because there are that many rows per week). if you had six rows, you would change that to 6, for example. Using October as an example, if there are 5 weeks in a month, there would technically be 35 times "10" would show up, 35/7 = 5....

This is a little problematic too, because depending on how you consider your weeks/month, it might disclude, or include months based on the fact that the Monday's date is for the previous month. Example if Monday is 01/31/2020, it would consider that week in January. I am not sure how exactly you'd want to tackle this. One way could be to do it based on week of the year, but that also has it's own issues. But then the formulas for all the items would have to change to reflect the way you're calculating your weeks, since it wouldn't be based on the month of the 1st date anymore.

I don't know if any of this made sense to you.... since I basically just rambled a whole lot ?...

 

[p_s]

For the Total Interviews, for the current data set you have 0 Interviews that week and 3 total interviews.... which is why I wasn't sure how you wanted to do that.... If you're manually filling out the total interviews (life time) per week, then it might make sense to do the MAXIFS function.

 

[WCAconsulting]

For the Total Interviews, for the current data set you have 0 Interviews that week and 3 total interviews.... which is why I wasn't sure how you wanted to do that.... If you're manually filling out the total interviews (life time) per week, then it might make sense to do the MAXIFS function.

Ok that makes since and I believe I have a handle on that for how the average is calculating and yes a MAX function will work.
I was filling some information out and did run across another issue that I am trying to fix now and I believe it will have to be a lookup function somehow. When I change a person from inactive to active, the columns associated with them on the MasterNum tab do not follow them and cause all columns to be inaccurate once the person is added into the header. Would you know a quick solution to this one? I do apologize for the questions and asking for help, I am just not that advanced of a user and neither is anyone in the group I am working with.

 

[p_s]

Does this happen in the sheet I copied? I used query to populate my masternum and workseet. It should be foolproofed as long as "active" is spelled correctly.

 

[WCAconsulting]

Does this happen in the sheet I copied? I used query to populate my masternum and workseet. It should be foolproofed as long as "active" is spelled correctly.

Yes, the query function does work correctly for the first 3 rows, however the physical data below that does not transfer with it. So if the first person is changed to inactive, the name disappears but the data does not throwing it off. I have it working by manually adding and deleting rows, just dont know if anyone else will be able to get it going.

 

[p_s]

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.