Formula to look at column C and Row A if match find cell and deduct of total how to do?

[Patriot2879]

Hi I hopeyou can help me please, I want a formula to look at a selected date and area,and if match to deduct the number what has been inputted in cell AC5 and enter thenew total in matching cell.
In columnsB2 to R2 I have the ‘dates’.
In Rows A3to A18 I have the ‘areas’.
In rows/columnsB3 to R18 I have numbers.

In Cell AC3I have a dropdown list with ‘dates’
In Cell AC4I have a dropdown list for ‘areas’.
In AC5 thisis where we enter a number.

What I wantis when a date is selected in AC3 and a area is selected in AC4 then a numberis inputted in AC5 I then want the corresponding cell to be updated In rows/columnsB3 to R18,

So forexample if I choose 04/09/2019 as a date and LOND as an area then enter anumber 5, I want the computer to find 04/09/2019 and LOND then if currently thenumber in that cell is 7 I want it to -5 and update the new number to 2.

Hope thismakes sense and you can help me with the formula.

Would it belike a IF or MATCH formula?

 

[Patriot2879]

hi I did ctrl g again and pasted in Application.EnableEvents = True under immediate hop this I correct?, but i still didnt work

 

[Patriot2879]

I hope you can help me please? I have attached the file.

 

[Patriot2879]

Hi Good morning all, just wondering if you can help please I have attached the file if you can have a look please?
https://www.dropbox.com/s/b8u39deou5...est2.xlsx?dl=0

 

[James006]

Hello,

You could test following

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, errorFound
On Error GoTo errorFound
If Target.Address <> "$AC$6" Then Exit Sub
Application.EnableEvents = False
    i = Application.Match(Range("AC3"), Range("A1:A18"), 0)
    j = Application.Match(Range("AC4"), Range("A2:R2"), 0)
    
    Cells(i, j).Value = Cells(i, j).Value - Range("AC6").Value
                
errorFound:
Application.EnableEvents = True
End Sub

Hope this will help

 

[James006]

Re,

By the way ... regarding your formula in cell AC5 ...

Would recommend following

=IFERROR(INDEX($A$1:$R$18,MATCH(AC3,$A$1:$A$18,0),MATCH(AC4, $A$2:$R$2, 0)),"")

 

[Patriot2879]

Hi thank you so much this works great, is there any way to clear AC3 to AC6 after the update? thank you again.
Please n you advise what is different with the formula as well for reference? Is It more secure?

 

[James006]

Hi,

Glad you could fix your problem ...

To clear cells, you can have

Range("AC3:AC4,AC6").ClearContents

Using the Index / Match gives you more flexibility ... and IfError will prevent you from having #N/A ... whenever you are clearing input cells ...

Hope this explains ...

 

[Patriot2879]

thank you where would I put the code above in the coding? iferror sounds a lot better as well I will use this more often

 

[James006]

Glad you like the Iferror function ...:wink:

Regarding the modified macro

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, errorFound
On Error GoTo errorFound
If Target.Address <> "$AC$6" Then Exit Sub
Application.EnableEvents = False
    ' Determine Row # and Column #
    i = Application.Match(Range("AC3"), Range("A1:A18"), 0)
    j = Application.Match(Range("AC4"), Range("A2:R2"), 0)
    
    ' Adjust the Intersection cell Value by substracting Input in AC6
    Cells(i, j).Value = Cells(i, j).Value - Range("AC6").Value
    
    ' Clear all Input cells
    Range("AC3:AC4,AC6").ClearContents
                
errorFound:
Application.EnableEvents = True
End Sub

 

[Patriot2879]

Hi I just amended the code to clear contents to only clear AC6 but excel keeps on crashing now after I press enter when I have inputted a number in AC6