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

J

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?
 
Sort by date Sort by votes

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
RoryA said:
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.
Click to expand...
No problem thank you very much if you need any more information let me know
 
Upvote 0
RoryA said:
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.
Click to expand...
Did you manage to make any progress?
 
Upvote 0
The code would be something like this (right click the 2022.23 IIF sheet tab, choose View code, and paste this in):

VBA Code: 
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
 
Upvote 0
RoryA said:
The code would be something like this (right click the 2022.23 IIF sheet tab, choose View code, and paste this in):

VBA Code: 
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
Click to expand...
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
BCDEFGHIJKLMN
22023
3JanuaryFebrauryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
4VI-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
5VI-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
6VI-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
7HI-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
8HI-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
9CVD-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
10CVD-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
11CVD-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
12CVD-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
13CVD-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
14CVD-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
15PC-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
16EHCH-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
17EHCH-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
18EHCH-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
19EHCH-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
20AC-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
21ACC-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
22ACC-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
23ACC-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
24ACC-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
25ACC-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
26SMR-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
27SMR-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
28SMR-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
29SMR-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
30SMR-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
31SMR-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
32SMR-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
33SMR-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
34SMR-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
35RESP-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
36RESP-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
37ES-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
38ES-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
39CAN-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
RangeFormula
F4:N4F4=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P15,0)
F5:N5F5=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P18,0)
F6:N6F6=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P21,0)
F7:N7F7=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P24,0)
F8:N8F8=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P27,0)
F9:N9F9=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P30,0)
F10:N10F10=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P33,0)
F11:N11F11=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P36,0)
F12:N12F12=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P39,0)
F13:N13F13=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P42,0)
F14:N14F14=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P45,0)
F15:N15F15=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P48,0)
F16:N16F16=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P51,0)
F17:N17F17=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P54,0)
F18:N18F18=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P57,0)
F19:N19F19=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:N20F20=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:N21F21=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P72,0)
F22:N22F22=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P75,0)
F23:N23F23=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P78,0)
F24:N24F24=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P81,0)
F25:N25F25=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P84,0)
F26:N26F26=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P87,0)
F27:N27F27=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P90,0)
F28:N28F28=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P93,0)
F29:N29F29=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P96,0)
F30:N30F30=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P99,0)
F31:N31F31=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P102,0)
F32:N32F32=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P105,0)
F33:N33F33=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P108,0)
F34:N34F34=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P111,0)
F35:N35F35=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P114,0)
F36:N36F36=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P117,0)
F37:N37F37=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P120,0)
F38:N38F38=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P123,0)
F39:N39F39=IF(AND('2022.23 IIF'!$B$10=Sheet4!F$3, Sheet4!$C$2='2022.23 IIF'!$C$10),'2022.23 IIF'!$P126,0)
 
Upvote 0
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:

VBA Code: 
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
 
Upvote 0
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
BCDEFGHIJKLMNOPQRSTUVWXYZ
220232024
3JanuaryFebrauryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebrauryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
4VI-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
5VI-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
6VI-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
7HI-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
8HI-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
9CVD-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
10CVD-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
11CVD-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
12CVD-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
13CVD-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
14CVD-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
15PC-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
16EHCH-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
17EHCH-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
18EHCH-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
19EHCH-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
20AC-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
21ACC-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
22ACC-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
23ACC-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
24ACC-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
25ACC-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
26SMR-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
27SMR-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
28SMR-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
29SMR-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
30SMR-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
31SMR-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
32SMR-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
33SMR-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
34SMR-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
35RESP-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
36RESP-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
37ES-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
38ES-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
39CAN-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
 
Upvote 0
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?
 
Upvote 0
RoryA said:
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?
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Pivot Slicer Help
Replies
2
Views
259
Trevor G
Trevor G
S
  • Question Question
SUM and CHOOSE formula question
Replies
7
Views
240
Fluff
Fluff
R
  • Question Question
Excel Creation Help
Replies
1
Views
222
awoohaw
awoohaw
trishcollins
  • Question Question
Looking for formula/VBA to determine where to best put extra funds
Replies
1
Views
235
trishcollins
trishcollins
A
  • Solved
Values Mismatch
Replies
3
Views
185
Alex Blakenburg
A

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

Share this page

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