I had a post similar to this a month ago but now need updated help with a similar problem. I have had issues understanding and knowing how to adapt the code to work with my new brief and other uses.
I have data in a table in Sheet 1 seen below, this is mostly user inputted with some functions which take the user-inputted data and calculates other values.
What I need is a way for the data from sheet 1 to be transferred either automatically or through the press/click of a button to sheet 2 which contains two tables which stores the values in column O and P. The data should be entered into the correct indicator row and the correct month. The user is able to select a month using a drop-down list in cell B10. My hope is that this works and I did get it working previously with help from this forum. I have tried coding this myself but haven't got it working and keep getting subscript and mismatch errors. Any advice on how to achieve this? I believe a Macro button is the way forward as a IF FALSE "DO NOTHING" is not easy to do in Excel.
I have data in a table in Sheet 1 seen below, this is mostly user inputted with some functions which take the user-inputted data and calculates other values.
| ToolT (version 3).xlsb.xlsm | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 9 | Month | ||||||||||||||||
| 10 | April | ||||||||||||||||
| 11 | Impact and Investment Fund (IIF) 2023/24 - targets and £ estimates | ||||||||||||||||
| 12 | |||||||||||||||||
| 13 | Indicator ref | Baseline (where req) | 2022/23 IIF Indicators | Trend direction | Targets | Actuals | Maximum points | £ per point | List size adjustment (estimate) | Prevalence adjustment (unknown) | Max value | Points earned | Approx value | ||||
| 14 | VI-02 | Percentage of patients aged 18 to 64 years and in a clinical at-risk group who received a seasonal influenza vaccination between 1 September 2023 and 31 March 2024 | é | Denominator | Lower threshold | Upper threshold | Achievement | 0.41579616 | PCN prev/ Nat prev | ||||||||
| 15 | 1,161 | 72% | 90% | 86% | 113 | £198.00 | 0.42 | 0.05719 | £9,303.02 | 87.64 | £412.65 | ||||||
| 16 | 836 | 1,045 | 998 | ||||||||||||||
| 17 | VI-03 | Percentage of children aged 2 to 3 who received a seasonal influenza vaccination between 1 September and 31 March | é | Denominator | Lower threshold | Upper threshold | Achievement | ||||||||||
| 18 | 238 | 64% | 82% | 75% | 20 | £198.00 | 0.42 | 0.01172 | £1,646.55 | 11.99 | £11.57 | ||||||
| 19 | 152 | 195 | 178 | ||||||||||||||
| 20 | HI-03 | Percentage of patients on the QOF Learning Disability register aged 14 years or over, who received a learning disability Annual Health Check and have a completed Health Action Plan in addition to a recording of ethnicity | é | Denominator | Lower threshold | Upper threshold | Achievement | ||||||||||
| 21 | 92 | 60% | 80% | 65% | 36 | £198.00 | 0.42 | 0.00453 | £2,963.80 | 9.39 | £3.50 | ||||||
| 22 | 55 | 74 | 60 | ||||||||||||||
| 23 | CAN-02 | Percentage of lower gastrointestinal two week wait (fast track) cancer referrals accompanied by a faecal immunochemical test result, with the result recorded in the twenty-one days leading up to the referral. | é | Denominator | Lower threshold | Upper threshold | Achievement | ||||||||||
| 24 | 154 | 65% | 80% | 72% | 22 | £198.00 | 0.42 | 0.00759 | £1,811.21 | 10.38 | £6.48 | ||||||
| 25 | 100 | 123 | 111 | ||||||||||||||
| 26 | ACC-08 | Percentage of appointments where time from booking to appointment was two weeks or less | é | Denominator | Lower threshold | Upper threshold | Achievement | ||||||||||
| 27 | 100 | 85% | 90% | 86% | 71 | £198.00 | 0.42 | 0.00493 | £5,845.26 | 14.20 | £5.76 | ||||||
| 28 | 85 | 90 | 86 | ||||||||||||||
| 29 | Totals | 262 | £198.00 | £0.42 | £0.02 | £21,569.84 | 133.60 | £439.97 | |||||||||
IIF Input | |||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L14 | L14 | =B5/B7 |
| I15,I27,I24,I21,I18 | I15 | =I16/F15 |
| L15 | L15 | =L14 |
| M15 | M15 | =F15/B5 |
| N15,N27,N24,N21,N18 | N15 | =J15*K15*L15 |
| O15,O27,O24,O21,O18 | O15 | =IF(I15<G15,0,IF(I15<H15,(J15*((G15-I15)/(G15-H15))),IF(I15>=H15,J15))) |
| P15,P27,P24,P21,P18 | P15 | =IF(M15="",O15*K15*L15,O15*K15*M15*L15) |
| G16,G28,G25,G22,G19 | G16 | =F15*G15 |
| H16,H28,H25,H22,H19 | H16 | =F15*H15 |
| L18 | L18 | =L14 |
| M18 | M18 | =F18/B5 |
| L21 | L21 | =L14 |
| M21 | M21 | =F21/B5 |
| L24 | L24 | =L14 |
| M24 | M24 | =F24/B5 |
| L27 | L27 | =L14 |
| M27 | M27 | =F27/B5 |
| J29,N29 | J29 | =SUM(J14:J28) |
| K29:M29 | K29 | =AVERAGE(K27,K24,K21,K18,K15) |
| O29:P29 | O29 | =SUMIF(O14:O28,"<>#DIV/0!") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| I24 | Expression | =IF(I25="",TRUE) | text | NO |
| I24 | Expression | =IF(I24>=H24,TRUE) | text | NO |
| I24 | Expression | =IF(I24>=G24,TRUE) | text | NO |
| I24 | Expression | =IF(I24<G24,TRUE) | text | NO |
| I27 | Expression | =IF(I28="",TRUE) | text | NO |
| I27 | Expression | =IF(I27>=H27,TRUE) | text | NO |
| I27 | Expression | =IF(I27>=G27,TRUE) | text | NO |
| I27 | Expression | =IF(I27<G27,TRUE) | text | NO |
| I15 | Expression | =IF(I16="",TRUE) | text | NO |
| I15 | Expression | =IF(I15>=H15,TRUE) | text | NO |
| I15 | Expression | =IF(I15>=G15,TRUE) | text | NO |
| I15 | Expression | =IF(I15<G15,TRUE) | text | NO |
| I18 | Expression | =IF(I19="",TRUE) | text | NO |
| I18 | Expression | =IF(I18>=H18,TRUE) | text | NO |
| I18 | Expression | =IF(I18>=G18,TRUE) | text | NO |
| I18 | Expression | =IF(I18<G18,TRUE) | text | NO |
| I21 | Expression | =IF(I22="",TRUE) | text | NO |
| I21 | Expression | =IF(I21>=H21,TRUE) | text | NO |
| I21 | Expression | =IF(I21>=G21,TRUE) | text | NO |
| I21 | Expression | =IF(I21<G21,TRUE) | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B10 | List | April,May,June,July,August,September,October,November,December,January,February,March, |
| F15:F16 | Any value | |
| I16 | Any value | |
| F18:F19 | Any value | |
| I19 | Any value | |
| F21:F22 | Any value | |
| I22 | Any value | |
| F24:F25 | Any value | |
| I25 | Any value | |
| F27:F28 | Any value | |
| I28 | Any value | |
| L14 | Any value | |
| ToolT (version 3).xlsb.xlsm | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | ||||||||||||||||
| 2 | Point Achievement 2023/24 | |||||||||||||||
| 3 | April | May | June | July | August | September | October | November | December | January | Febraury | March | ||||
| 4 | VI-02 | |||||||||||||||
| 5 | VI-03 | |||||||||||||||
| 6 | HI-03 | |||||||||||||||
| 7 | CAN-02 | |||||||||||||||
| 8 | ACC-08 | |||||||||||||||
| 9 | ||||||||||||||||
| 10 | Funding Achievement 2023/24 | |||||||||||||||
| 11 | April | May | June | July | August | September | October | November | December | January | Febraury | March | ||||
| 12 | VI-02 | |||||||||||||||
| 13 | VI-03 | |||||||||||||||
| 14 | HI-03 | |||||||||||||||
| 15 | CAN-02 | |||||||||||||||
| 16 | ACC-08 | |||||||||||||||
| 17 | ||||||||||||||||
| 18 | ||||||||||||||||
| 19 | ||||||||||||||||
| 20 | ||||||||||||||||
| 21 | ||||||||||||||||
IIF Summary | ||||||||||||||||
What I need is a way for the data from sheet 1 to be transferred either automatically or through the press/click of a button to sheet 2 which contains two tables which stores the values in column O and P. The data should be entered into the correct indicator row and the correct month. The user is able to select a month using a drop-down list in cell B10. My hope is that this works and I did get it working previously with help from this forum. I have tried coding this myself but haven't got it working and keep getting subscript and mismatch errors. Any advice on how to achieve this? I believe a Macro button is the way forward as a IF FALSE "DO NOTHING" is not easy to do in Excel.
