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]

Is anyone able to help? It may just be the task of adapting this code to fit the new requirements, but I am unsure how to proceed with this.

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.