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

JKM00

New Member
Joined
Mar 10, 2023
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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?
 
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):

VBA Code:
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.
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top