Excel Function Help Needed, for an IF FALSE "DO NOTHING" POSSIBILITY

[JKM00]

Dear All,

I currently have a spreadsheet which is to be used by others to track their monthly and quarterly payments.

On sheet1 I have set up formulas for the user to input their monthly points data, and my formulas use this data to calculate the corresponding funding achievement in Column P for that month. At the top of this sheet, I have set up a simple list so that the user may select the month and year for which the data is relevant.

On sheet2 I aim to pull all of the funding data in column P and insert it into a table with year and month at the top and the different payment categories on the side.
The current code I have used is an IF statement "=IF(AND('Sheet1'!$B$10=Sheet2!C$3, Sheet2!$C$2='Sheet1'!$C$10),'Sheet1'!$P15,0)" where Sheet1 B10 and C10 are the drop down lists for month and year and Sheet2 C2 is the Year and C3-N3 are the months. The code should mean that when the Table headings (Year and Month) for a column in Sheet2 are equal to the selected year and month in Sheet1 then the value is inserted into the cell and if it isn't equal a zero is inputted.

This code works for one month, so if the year and month are set to 'January 2023' in sheet 1 the payment information fills into the table correctly in Sheet2. However, when the month is changed to February 2023 the payment data fills correctly into the February column which is as intended, but the January column changes to all 0s which is unwanted.

I understand that this is what the formula is telling excel to do, but this isn't my intended outcome. I need some kind of formula or code so that when the Year and Month selected in Sheet1 are equal to eg Jan 2023 the values are filled into the Table in Sheet 2 and when it doesn't equal Jan 2023 eg Feb 2023, the values are left as they were when it was equal to Jan 2023.

Is this possible in Excel and could anyone recommend how this can be implemented?

 

[RoryA]

You could use something like this (in a normal module):

Sub UpdateData()
      Dim formulaSheet As Worksheet
      Set formulaSheet = ThisWorkbook.Sheets("Sheet4")
      With formulaSheet
         Dim yearMatch
         yearMatch = Application.Match(Range("C10").Value, .Range("2:2"), 0)
       
         Dim monthMatch
         monthMatch = Application.Match(Range("B10").Value, .Range(.Cells(3, yearMatch), .Cells(3, .Columns.Count)), 0)
         monthMatch = yearMatch + monthMatch - 1
       
         Dim lastRow As Long
         lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
       
         With .Range(.Cells(4, monthMatch), .Cells(lastRow, monthMatch))
            .FormulaR1C1 = "=IF(AND('2022.23 IIF'!R10C2=R3C, R2C3='2022.23 IIF'!R10C" & yearMatch & "),INDEX('2022.23 IIF'!C16,MATCH(RC2,'2022.23 IIF'!C2,0)+1),0)"
            .Value2 = .Value2
         End With
          
      End With
 
End Sub

then add a form button to the sheet and assign this macro to it.

 

[JKM00]

You could use something like this (in a normal module):

Sub UpdateData()
      Dim formulaSheet As Worksheet
      Set formulaSheet = ThisWorkbook.Sheets("Sheet4")
      With formulaSheet
         Dim yearMatch
         yearMatch = Application.Match(Range("C10").Value, .Range("2:2"), 0)
       
         Dim monthMatch
         monthMatch = Application.Match(Range("B10").Value, .Range(.Cells(3, yearMatch), .Cells(3, .Columns.Count)), 0)
         monthMatch = yearMatch + monthMatch - 1
       
         Dim lastRow As Long
         lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
       
         With .Range(.Cells(4, monthMatch), .Cells(lastRow, monthMatch))
            .FormulaR1C1 = "=IF(AND('2022.23 IIF'!R10C2=R3C, R2C3='2022.23 IIF'!R10C" & yearMatch & "),INDEX('2022.23 IIF'!C16,MATCH(RC2,'2022.23 IIF'!C2,0)+1),0)"
            .Value2 = .Value2
         End With
          
      End With
 
   End If
End Sub

then add a form button to the sheet and assign this macro to it.

I think I have set up the macro correctly and the button but I am getting an error of 'Compile Error: End If without Block If'

 

[RoryA]

Sorry - I forgot to remove the End If line above the End Sub when converting it. (I will edit my previous post now).

 

[JKM00]

The button is working for 2023 but not for 2024 or 2025. Are there any alterations to the code to make it work for multiple years?

 

[RoryA]

Where is the output for 2024 and 2025?

Edit - never mind, dumb error on my part. The formula line should be:

            .FormulaR1C1 = "=IF(AND('2022.23 IIF'!R10C2=R3C, R2C" & yearMatch & "='2022.23 IIF'!R10C3),INDEX('2022.23 IIF'!C16,MATCH(RC2,'2022.23 IIF'!C2,0)+1),0)"

 

[JKM00]

Perfect, I have it working now. I have more sheets to add to this workbook, so you may see a post again if I need assistance adapting the code later, but I'll do it seperately. Thank you for all your help!

 

[JKM00]

Perfect, I have it working now. I have more sheets to add to this workbook, so you may see a post again if I need assistance adapting the code later, but I'll do it seperately. Thank you for all your help!

Actually, can you advise how I would update the code if I added another year say 2025 to my table

 

[RoryA]

The code should work for additional years as long as your data format for each year is the same.

 

[JKM00]

When I try to run the macro I get an error 'Run-time error '9': Subscript out of range'

Update: Fixed this now works perfectly well thank you

 

[JKM00]

ToolT (version 2).xlsb.xlsm
	A	B	C	D	E	F	G	H
1	Month	Year
2	April	2024
3
4	QOF Category	Achieved
5	Atrial Fibrillation	£1,000.00
6	Asthma	£500.00
7	Cancer	£210.00
8	Secondary prevention of coronary heart disease	£235.00
9	Chronic kidney disease	£16.00
10	Chronic obstructive pulmonary disease	£16.00
11	Dementia	£13.00
12	Depression	£3.00
13	Diabetes mellitus	£21.00
14	Epilepsy	£165.00
15	Heart Failure	£16.00
16	Hypertension	£1.00
17	Learning Disability	£61.00
18	Mental Health	£1.00
19	Non-diabetic hyperglycaemia	£61.00
20	Osteoporosis: secondary prevention of fragility fractures	£1.00
21	Peripheral arterial disease	£3.00
22	Palliative care	£1.00
23	Rheumatoid arthritis	£35.00
24	Stroke and transient ischaemic attack	£165.00
25	Blood Pressure	£1.00
26	Obesity	£5.00
27	Smoking	£36.00
28	Cervical Screening	£5.00
29	Vaccination and Immunisation	£15.00
30	Prescription Drug Dependency	£5.00
31	Optimising Access	£51.00
32
33
34
35
QOF Data Input

Cells with Data Validation
Cell	Allow	Criteria
A2	List	January,February,March,April,May,June,July,August,September,October,November,December
B2	List	2023,2024,2025

ToolT (version 2).xlsb.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		2023												2024
2		January	February	March	April	May	June	July	August	September	October	November	December	January	February	March	April	May	June	July	August	September	October	November	December
3	Atrial Fibrillation				£1,000.00
4	Asthma				£500.00
5	Cancer				£210.00
6	Secondary prevention of coronary heart disease				£235.00
7	Chronic kidney disease				£16.00
8	Chronic obstructive pulmonary disease				£16.00
9	Dementia				£13.00
10	Depression				£3.00
11	Diabetes mellitus				£21.00
12	Epilepsy				£165.00
13	Heart Failure				£16.00
14	Hypertension				£1.00
15	Learning Disability				£61.00
16	Mental Health				£1.00
17	Non-diabetic hyperglycaemia				£61.00
18	Osteoporosis: secondary prevention of fragility fractures				£1.00
19	Peripheral arterial disease				£3.00
20	Palliative care				£1.00
21	Rheumatoid arthritis				£35.00
22	Stroke and transient ischaemic attack				£165.00
23	Blood Pressure				£1.00
24	Obesity				£5.00
25	Smoking				£36.00
26	Cervical Screening				£5.00
27	Vaccination and Immunisation				£15.00
28	Prescription Drug Dependency				£5.00
29	Optimising Access				£51.00
QOF Summary

Hello again, I have another issue with the same workbook. I am trying to do something very similar to my previous issue. Where I have a dropdown list in sheet 1 "QOF Data Input" for months in cell A2 (Jan-Dec) and years in B2 (2023, 2024, 2025). Then a table below with category names and spaces for a user to input monthly or quarterly data. What I need again is code which transfers the input data from the selected month and year and once the button is clicked it transfers to a new table in sheet 2 ("QOF Summary") which would allow the user to compare monthly for a number of years. The code I tried to use had issues finding the months and years.