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?

 

[JKM00]

Any thoughts?

 

[RoryA]

Apologies - it has been a hectic weekend. You will need code for this - I'll try and create a mock up of your workbook and write some code for it later.

 

[JKM00]

Apologies - it has been a hectic weekend. You will need code for this - I'll try and create a mock up of your workbook and write some code for it later.

No problem thank you very much if you need any more information let me know

 

[JKM00]

Apologies - it has been a hectic weekend. You will need code for this - I'll try and create a mock up of your workbook and write some code for it later.

Did you manage to make any progress?

 

[RoryA]

The code would be something like this (right click the 2022.23 IIF sheet tab, choose View code, and paste this in):

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("B10")) Is Nothing Then
      Dim formulaSheet As Worksheet
      Set formulaSheet = ThisWorkbook.Sheets("Sheet4")
      With formulaSheet
         Dim yearMatch
         yearMatch = Application.Match(Me.Range("C10").Value, .Range("2:2"), 0)
         
         Dim monthMatch
         monthMatch = Application.Match(Me.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, R2C" & yearMatch & "='2022.23 IIF'!R10C3),'2022.23 IIF'!R[11]C16,0)"
            .Value2 = .Value2
         End With
            
      End With
   
   End If
End Sub

 

[JKM00]

The code would be something like this (right click the 2022.23 IIF sheet tab, choose View code, and paste this in):

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("B10")) Is Nothing Then
      Dim formulaSheet As Worksheet
      Set formulaSheet = ThisWorkbook.Sheets("Sheet4")
      With formulaSheet
         Dim yearMatch
         yearMatch = Application.Match(Me.Range("C10").Value, .Range("2:2"), 0)
        
         Dim monthMatch
         monthMatch = Application.Match(Me.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, R2C" & yearMatch & "='2022.23 IIF'!R10C3),'2022.23 IIF'!R[11]C16,0)"
            .Value2 = .Value2
         End With
           
      End With
  
   End If
End Sub

This is almost perfect, the values stay when the month is moved, and I can go back to previous months to make changes, and they are updated. However, the only issue I have is that the values column in the first sheet is made of multiple rows, and so I am left with a table in Sheet4 like this with gaps. The values currently in HI-01 should be in VI-02 and CVD-02 should be in VI-03 etc.

As I am a beginner at coding, could you explain what the lines or blocks of code do so I might make some adjustments and write my own for other sheets in the workbook and hopefully rectify this error?

ToolT.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
2		2023
3		January	Febraury	March	April	May	June	July	August	September	October	November	December
4	VI-01	£3,326.37	£3,326.37	£3,326.37	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
5	VI-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
6	VI-03	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
7	HI-01	£6,422.19	£6,422.19	£6,422.19	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
8	HI-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
9	CVD-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
10	CVD-02	£540.73	£540.73	£540.73	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
11	CVD-03	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
12	CVD-04	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
13	CVD-05	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
14	CVD-06	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
15	PC-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
16	EHCH-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
17	EHCH-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
18	EHCH-04	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
19	EHCH-06	£5,904.31	£2,026.29	£2,026.29	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
20	AC-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
21	ACC-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
22	ACC-05	£1,175.70	£1,175.70	£1,175.70	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
23	ACC-07	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
24	ACC-08	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
25	ACC-09	£2,577.94	£2,577.94	£2,577.94	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
26	SMR-01A	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
27	SMR-01B	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
28	SMR-01C	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
29	SMR-01D	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
30	SMR-02A	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
31	SMR-02B	£3,345.38	£3,345.38	£3,345.38	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
32	SMR-02C	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
33	SMR-02D	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
34	SMR-03	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
35	RESP-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
36	RESP-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
37	ES-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
38	ES-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
39	CAN-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
Sheet4

Cell Formulas
Range	Formula
F4:N4	F4	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P15,0)
F5:N5	F5	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P18,0)
F6:N6	F6	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P21,0)
F7:N7	F7	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P24,0)
F8:N8	F8	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P27,0)
F9:N9	F9	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P30,0)
F10:N10	F10	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P33,0)
F11:N11	F11	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P36,0)
F12:N12	F12	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P39,0)
F13:N13	F13	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P42,0)
F14:N14	F14	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P45,0)
F15:N15	F15	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P48,0)
F16:N16	F16	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P51,0)
F17:N17	F17	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P54,0)
F18:N18	F18	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P57,0)
F19:N19	F19	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),SUM('2022.23 IIF'!$P60,'2022.23 IIF'!$P63),0)
F20:N20	F20	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),SUM('2022.23 IIF'!$P66,'2022.23 IIF'!$P69),0)
F21:N21	F21	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P72,0)
F22:N22	F22	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P75,0)
F23:N23	F23	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P78,0)
F24:N24	F24	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P81,0)
F25:N25	F25	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P84,0)
F26:N26	F26	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P87,0)
F27:N27	F27	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P90,0)
F28:N28	F28	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P93,0)
F29:N29	F29	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P96,0)
F30:N30	F30	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P99,0)
F31:N31	F31	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P102,0)
F32:N32	F32	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P105,0)
F33:N33	F33	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P108,0)
F34:N34	F34	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P111,0)
F35:N35	F35	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P114,0)
F36:N36	F36	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P117,0)
F37:N37	F37	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P120,0)
F38:N38	F38	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P123,0)
F39:N39	F39	=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P126,0)

 

[RoryA]

It appears, from your sample, that the right row can be found by finding the data from column B on sheet 4 in column B on the source sheet and then going down one row (due to merged cells), so you can adapt the formula accordingly like this:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("B10")) Is Nothing Then
      Dim formulaSheet As Worksheet
      Set formulaSheet = ThisWorkbook.Sheets("Sheet4")
      With formulaSheet
         Dim yearMatch
         yearMatch = Application.Match(Me.Range("C10").Value, .Range("2:2"), 0)
         
         Dim monthMatch
         monthMatch = Application.Match(Me.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

 

[JKM00]

Is there anyway to get the code to work for years as well as months, currently if I change the year to 2024 in C10 Sheet1 the data isn't inputted into the column

ToolT.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2		2023												2024
3		January	Febraury	March	April	May	June	July	August	September	October	November	December	January	Febraury	March	April	May	June	July	August	September	October	November	December
4	VI-01	£3,326.37	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
5	VI-02	£6,422.19	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
6	VI-03	£540.73	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
7	HI-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
8	HI-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
9	CVD-01	£2,026.29	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
10	CVD-02	£1,175.70	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
11	CVD-03	£2,577.94	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
12	CVD-04	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
13	CVD-05	£3,345.38	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
14	CVD-06	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
15	PC-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
16	EHCH-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
17	EHCH-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
18	EHCH-04	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
19	EHCH-06	#N/A	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
20	AC-02	#N/A	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
21	ACC-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
22	ACC-05	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
23	ACC-07	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
24	ACC-08	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
25	ACC-09	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
26	SMR-01A	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
27	SMR-01B	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
28	SMR-01C	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
29	SMR-01D	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
30	SMR-02A	£332.64	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
31	SMR-02B	£332.64	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
32	SMR-02C	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
33	SMR-02D	£332.64	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
34	SMR-03	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
35	RESP-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
36	RESP-02	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
37	ES-01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
38	ES-02	£3,659.01	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
39	CAN-01	£279.18	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
Sheet4

 

[RoryA]

I thought about that earlier but the downside is that if you were going to change the year and month (eg you went from Dec 2023 to Jan 2024), the code would overwrite Dec 2024 with the current data set and then overwrite Jan 2024 when you changed the month. It might be better to have it as a button that you press rather than a worksheet change event, so that you can specify month and/or year before the code runs?

 

[JKM00]

I thought about that earlier but the downside is that if you were going to change the year and month (eg you went from Dec 2023 to Jan 2024), the code would overwrite Dec 2024 with the current data set and then overwrite Jan 2024 when you changed the month. It might be better to have it as a button that you press rather than a worksheet change event, so that you can specify month and/or year before the code runs?

Yes, I was thinking of this problem as well, do you know how I would go about creating such a button? I guess how it would work is it would look at the selected month and year and then print the values into the respective column instead of automatically updating as the code is currently.