shaunreeder
New Member
- Joined
- Aug 10, 2022
- Messages
- 3
- Office Version
- 365
- 2016
- Platform
- Windows
- MacOS
Hello,
New here and wondered if I could get some assistance with this.
I've been trying to figure this out for days and cant seem to get it right.
I have a Dashboard that will be used by multiple people to enter data for reporting.
The common issue is people using the same rows or leaving blank rows which messes up the pivot tables etc.
What I am trying to do is create two buttons on the top of the worksheet that will automatically go to and fill the next empty row(s) with the data needed for that report button selected with the parameters that the user inputs on the top like below:
Every Monday a weekly report is completed. Weekly Reports contain 2 metrics to report on.
Every FIRST Monday, monthly report are completed. Monthly Reports contain 8 metrics to report, including the weekly report with it.
Using the Weekly Button would result in it finding the next 2 empty rows on the table and filling Columns A,B,C & E (each row for E would contain the metric needed) like below:
For the Monthly button it would do the same thing as the weekly button but also add the monthly reports as well, adding the next 8 rows instead of 2 with the metrics for the weekly and monthly reports listed in Col. E like below:
Any help is appreciated. It seems pretty simple but I'm new and seem to cant get it ironed out.
New here and wondered if I could get some assistance with this.
I've been trying to figure this out for days and cant seem to get it right.
I have a Dashboard that will be used by multiple people to enter data for reporting.
The common issue is people using the same rows or leaving blank rows which messes up the pivot tables etc.
What I am trying to do is create two buttons on the top of the worksheet that will automatically go to and fill the next empty row(s) with the data needed for that report button selected with the parameters that the user inputs on the top like below:
Every Monday a weekly report is completed. Weekly Reports contain 2 metrics to report on.
Every FIRST Monday, monthly report are completed. Monthly Reports contain 8 metrics to report, including the weekly report with it.
Using the Weekly Button would result in it finding the next 2 empty rows on the table and filling Columns A,B,C & E (each row for E would contain the metric needed) like below:
For the Monthly button it would do the same thing as the weekly button but also add the monthly reports as well, adding the next 8 rows instead of 2 with the metrics for the weekly and monthly reports listed in Col. E like below:
Any help is appreciated. It seems pretty simple but I'm new and seem to cant get it ironed out.
| 2022 PCP Dashboard2.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Choose Report Type Here -> | Weekly | Choose Report Week Here -> | 1/17/2022 | Choose Provider Here -> | Test, Test | MONTHLY BUTTON HERE | WEEKLY BUTTONEHERE | ||||
| 2 | Report Type | Report Week/Month | Provider (Use drop down box) | Location | Objective | Actual | Comments | Month/Year | Target | Stretch | ||
| 3 | Weekly | 3/11/2024 | Salvo, Anthony | Hammonton | AtlantiCare First | 0.720 | 2024 | 0.600 | 0.615 | |||
| 4 | Weekly | 2/19/2024 | Vu, Dinh | Galloway South | Missing Charges | 0.650 | 2024 | 10.000 | 0.000 | |||
| 5 | Weekly | 1/15/2024 | Paradela, Ephrem Hector | EHT | AtlantiCare First | 0.618 | 2024 | 0.600 | 0.615 | |||
| 6 | Weekly | 1/22/2024 | Berlin, William | Tuckerton | Missing Charges | 0.000 | 2024 | 10.000 | 0.000 | |||
| 7 | Weekly | 2/19/2024 | Kerstetter, Kathleen | Galloway South | Missing Charges | 1.000 | 2024 | 1.000 | 0.000 | |||
| 8 | Weekly | 3/11/2024 | Adames Mendez, Widalys | Galloway South | Missing Charges | 2.000 | 2024 | 10.000 | 0.000 | |||
| 9 | Monthly | 7/4/2022 | Sharma, Sonia | Hammonton | Daily Visits | 1,500.000 | 2022 | 342.000 | 380.000 | |||
| 10 | Weekly | 1/8/2024 | Almanzar, Randy | Pleasantville | Missing Charges | 0.000 | 2024 | 10.000 | 0.000 | |||
| 11 | Monthly | 5/2/2022 | Bennett, Holly | Galloway South | No show rate | 0.300 | 2022 | 0.050 | 0.030 | |||
| 12 | Weekly | 1/29/2024 | Beenstock, Steven | Manahawkin | AtlantiCare First | 0.970 | 2024 | 0.400 | 0.415 | |||
| 13 | Weekly | 2/26/2024 | Barrios, Cristian | Manahawkin | Missing Charges | 0.000 | 2024 | 10.000 | 0.000 | |||
| 14 | Weekly | 1/30/2022 | Amin, Anila | Mays Landing - Geriatrics | Missing Charges | 1.000 | 2022 | 10.000 | 0.000 | |||
| 15 | Monthly | 3/7/2022 | Test, Test | EHT | Daily Visits | 372.000 | 2022 | 342.000 | 380.000 | |||
| 16 | Monthly | 8/1/2022 | Test, Test | EHT | Daily Visits | 250.000 | 2022 | 342.000 | 380.000 | |||
| 17 | Weekly | 8/29/2022 | Test, Test | EHT | Missing Charges | 0.000 | 2022 | 10.000 | 0.000 | |||
| 18 | Monthly | 4/4/2022 | Test, Test | EHT | Daily Visits | 192.000 | 2022 | 342.000 | 380.000 | |||
| 19 | Monthly | 5/2/2022 | Test, Test | EHT | Daily Visits | 205.000 | 2022 | 342.000 | 380.000 | |||
| 20 | Monthly | 7/4/2022 | Test, Test | EHT | Daily Visits | 126.000 | 2022 | 342.000 | 380.000 | |||
| 21 | Monthly | 7/4/2022 | Test, Test | EHT | Wellness Visit % Completion | 0.126 | 2022 | 0.430 | 0.440 | |||
| 22 | Monthly | 7/4/2022 | Test, Test | EHT | Panel size | 855.000 | 2022 | 1,600.000 | 1,800.000 | |||
| 23 | Monthly | 7/4/2022 | Test, Test | EHT | No show rate | 0.143 | 2022 | 0.050 | 0.030 | |||
| 24 | Monthly | 7/4/2022 | Test, Test | EHT | RVU Production | 281.000 | 2022 | 4,200.000 | 4,800.000 | |||
| 25 | Monthly | 8/1/2022 | Test, Test | EHT | No show rate | 0.000 | 2022 | 0.050 | 0.030 | |||
| 26 | Monthly | 8/1/2022 | Test, Test | EHT | RVU Production | 1,025.000 | 2022 | 4,200.000 | 4,800.000 | |||
| 27 | Monthly | 8/1/2022 | Test, Test | EHT | Panel size | 1,825.000 | 2022 | 1,600.000 | 1,800.000 | |||
| 28 | Monthly | 8/1/2022 | Test, Test | EHT | Rate the Provider Score | 0.856 | 2022 | 0.881 | 0.891 | |||
| 29 | Monthly | 8/1/2022 | Test, Test | EHT | Wellness Visit % Completion | 0.678 | 2022 | 0.430 | 0.440 | |||
| 30 | Monthly | 1/3/2022 | Test, Test | EHT | Daily Visits | 365.000 | 2022 | 342.000 | 380.000 | |||
| 31 | Weekly | 1/17/2022 | Test, Test | EHT | Missing Charges | 0.000 | 2022 | 10.000 | 0.000 | |||
| 32 | Monthly | 5/2/2022 | Test, Test | EHT | Rate the Provider Score | 0.956 | 2022 | 0.881 | 0.891 | |||
| 33 | Weekly | 1/3/2022 | Test, Test | EHT | AtlantiCare First | 2022 | 0.600 | 0.615 | ||||
| 34 | Weekly | 1/3/2022 | Test, Test | EHT | Missing Charges | 2022 | 10.000 | 0.000 | ||||
| 35 | Monthly | 1/3/2022 | Test, Test | EHT | Daily Visits | 2022 | 342.000 | 380.000 | ||||
| 36 | Monthly | 1/3/2022 | Test, Test | EHT | No show rate | 2022 | 0.050 | 0.030 | ||||
| 37 | Monthly | 1/3/2022 | Test, Test | EHT | Panel size | 2022 | 1,600.000 | 1,800.000 | ||||
| 38 | Monthly | 1/3/2022 | Test, Test | EHT | Rate the Provider Score | 2022 | 0.881 | 0.891 | ||||
| 39 | Monthly | 1/3/2022 | Test, Test | EHT | RVU Production | 2022 | 4,200.000 | 4,800.000 | ||||
| 40 | Monthly | 1/3/2022 | Test, Test | EHT | Wellness Visit % Completion | 2022 | 0.430 | 0.440 | ||||
| 41 | ||||||||||||
Data Entry 2022 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H3:H41 | H3 | =IF($B3="","",TEXT($B3,"YYYY")) |
| I3:I41 | I3 | =IF($E3="","",(IF($L3="Physician",INDEX(PHYSICIANTARGETS[Target],MATCH(1,(PHYSICIANTARGETS[Metric]=$E3)*IF($E3="Atlanticare First",$Q3=PHYSICIANTARGETS[FTE/Market],$M3=PHYSICIANTARGETS[FTE/Market]),0)),IF($L3="APN",INDEX(APNTARGETS[Target],MATCH(1,(APNTARGETS[Metric]=$E3)*IF($E3="Atlanticare First",$Q3=APNTARGETS[FTE/Market],$M3=APNTARGETS[FTE/Market]),0)))))) |
| J3:J41 | J3 | =IF($E3="","",IF($L3="Physician",INDEX(PHYSICIANTARGETS[Stretch],MATCH(1,(PHYSICIANTARGETS[Metric]=$E3)*IF($E3="Atlanticare First",$Q3=PHYSICIANTARGETS[FTE/Market],$M3=PHYSICIANTARGETS[FTE/Market]),0)),IF($L3="APN",INDEX(APNTARGETS[Stretch],MATCH(1,(APNTARGETS[Metric]=$E3)*IF($E3="Atlanticare First",$Q3=APNTARGETS[FTE/Market],$M3=APNTARGETS[FTE/Market]),0))))) |
| D3:D41 | D3 | =IFERROR(VLOOKUP($C3,ProviderTable,4,FALSE),"") |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F2 | Expression | =AND(ISBLANK($F2), NOT(ISBLANK($E2))) | text | YES |
| F2 | Expression | =ISBLANK($A2) | text | NO |
| D2 | Expression | =AND(ISBLANK($F2), NOT(ISBLANK($E2))) | text | YES |
| D2 | Expression | =ISBLANK($A2) | text | NO |
| B2 | Expression | =AND(ISBLANK($F2), NOT(ISBLANK($E2))) | text | YES |
| A3:Q49999 | Expression | =AND(ISBLANK($F3), NOT(ISBLANK($E3))) | text | YES |
| B3:G49999 | Expression | =ISBLANK($A3) | text | NO |
| H3:Q49999 | Expression | =ISBLANK($E3) | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| G13:G17 | List | =IF($A13="Weekly",Weekly,IF($A13="Monthly",Monthly,"Select Report Type First")) |
| G1 | List | =Provider_List |
| B1 | List | =INDIRECT("ReportingSchedule[Reporting Schedules]") |
| A3:A49999 | List | =INDIRECT("ReportingSchedule[Reporting Schedules]") |
| B3:B49999 | List | =IF($A3="Weekly",Weekly,IF($A3="Monthly",Monthly,"Select Report Type First")) |
| C3:C49999 | List | =Provider_List |
| E3:E41 | List | =IF($A3="Weekly",Weekly_List,IF($A3="Monthly",Monthly_List,"Select Report Type First")) |
| D1 | List | =IF($B1="Weekly",Weekly,IF($B1="Monthly",Monthly,"Select Report Type First")) |
