Forum Discussion
Aggregate Index AVERAGE/SUM
Haetrox Have a look at the attached workbook. If I understood your intentions correctly, you may use SUMIF in column N on the Calculation sheet. No need for such a complicated AGGREGATE/INDEX/ MATCH function.
Couldn't replicate the #VALUE error you mentioned. See cell M20 on the Calculation sheet.
Lastly, you mention that you want drop-down lists for the names. You need to create a list with all possible names first and refer to that list with Data Validation. Have demonstrated that in the attached workbook for the first two days in November.
Having said all that, I wonder why you chose this way to capture data. It's resembles noting down activities on a paper calendar, which is not the most effective way in Excel. Personally, I would create one table (can be for the whole year) with separate columns for Date, Provider, PPH, CT, PTs, No Show and Chart%. Then you can use built-in tools (like pivot tables and filters) to summarise the data in whatever format you need. Of course, you may have good reasons to set up the system as you did. Up to you.
Haetrox Have a look at the attached workbook. If I understood your intentions correctly, you may use SUMIF in column N on the Calculation sheet. No need for such a complicated AGGREGATE/INDEX/ MATCH function.
Couldn't replicate the #VALUE error you mentioned. See cell M20 on the Calculation sheet.
Lastly, you mention that you want drop-down lists for the names. You need to create a list with all possible names first and refer to that list with Data Validation. Have demonstrated that in the attached workbook for the first two days in November.
Having said all that, I wonder why you chose this way to capture data. It's resembles noting down activities on a paper calendar, which is not the most effective way in Excel. Personally, I would create one table (can be for the whole year) with separate columns for Date, Provider, PPH, CT, PTs, No Show and Chart%. Then you can use built-in tools (like pivot tables and filters) to summarise the data in whatever format you need. Of course, you may have good reasons to set up the system as you did. Up to you.
Thank you so much! Your SUMIF and the use of AVERAGEIF helps this so much! Also thank you for the DataValidation, I guess I missed that portion in that video and excel how to.
What would you suggest in regards to referencing sheets such as NOVEMBER/DECEMBER and making a list such as that of the sheet CALCULATIONS with this data as the Arrays don't move if I cut and move this around for new months?
Attached is the newest file as I cleaned up using the information you provided.
Haetrox Glad you did get SUMIF and AVERAGEIF to work and sorted out the data validation bit. With respect to referencing the monthly sheets, I'd get rid of them all together.
As mentioned in my previous post, I don't really understand why you use these monthly sheets as the are difficult to maintain and summarise. Isn't that exactly what you are experiencing? My suggestion is to go for a sequential list of "events" as described in my previous post and use built-in Excel functionality to summarise the data. Obviously, that means a complete redesign of your schedules.
By the way, did you notice that your NOVEMBER and DECEMBER sheets have two ranges for day 17? I suspect that it is not intentional. And, be aware that using merged cells is often causing trouble copying, cutting, pasting, filtering, sorting and referencing cells/ranges. Better to avoid the use of them.
