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]

I have so far got the code below that cross references the column (dates and rows with area) to get the cell total, but I don't know how to carry n from here by the deduction

=VLOOKUP(AC3, A2:R18, MATCH(AC4, A2:R2, 0),0)

 

[Patriot2879]

would I need to use a IF formula now? to deduct?

 

[jasonb75]

A formula can not 'send' information to another cell, it can only display a result in the cell that holds it.

Using your example, the cell that matches up to 4/9/19 and London holds the value of 7, so it can not hold a formula as well.

If that cell already holds a formula that is giving the result of 7 then that formula could be changed so that is subtracts the value in AC5 when the 2 dropdowns match.

Also, bear in mind that a formula result is not permanent, only the results based on what is in AC3, AC4 and AC5 now will be shown, any previous results are lost as soon as another change is made, so using your example again, assuming the 7 for 4/9/19 for London is the result of a formula, then if you change the date to 5/9/19 the figure for 4/5/19 would change back to 7.

 

[Patriot2879]

ok thank you for the advise would it be best doing a macro to do the subtraction and update? if so how would I do that?

22/08/2019

23/08/2019

26/08/2019

27/08/2019

28/08/2019

29/08/2019

30/08/2019

02/09/2019

03/09/2019

04/09/2019

05/09/2019

06/09/2019

09/09/2019

10/09/2019

11/09/2019

12/09/2019

13/09/2019

EELC N

3

5

6

Area

LOND

EELC S

Date

06/09/2019

EMEB

Available

8

LOND

8

Book

MANWEB

MIDE

NORTHERN

NORWEB N

NORWEB S

Scotland N

Scotland S

SEEB

SOUTH

SWALEC

SWEB

YEDL

13

<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;"> <col width="25" style="width: 19pt; mso-width-source: userset; mso-width-alt: 881;" span="17"> <col width="64" style="width: 48pt;" span="2"> <col width="119" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4238;"> <tbody> </tbody>

 

[Patriot2879]

I have now worked out with the formula how to match date with area and show the availability. I have now added a cell with appts to be booked which will be in AC6. how would I do the subtraction from availability in AC5 to subtract AC6 then update the correct cell with new number? can It be done in macro? please can you help?

 

[Patriot2879]

hi my range is Availability shown is in AC6, Date is A2:R18, Area is A2:R2, t whole range is A:R18, the jobs to b booked is in AC7, then to do the subtraction it is between AC6-AC7 ten it wil need to update the correct cell in the range off A2:R8 how could this be done In a macro? hope you can help.

 

[jasonb75]

See if this helps.
Please note that this is event code that needs to go into the correct worksheet module (right click the sheet tab ame in excel, then view code).
Remember to use a copy of your workbook for testing in order to prevent data loss should anything not be as expected.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aFound As Range, dFound As Range, errorFound
On Error GoTo errorFound
If Not Target.Address(0, 0) = "AC5" Then Exit Sub
Application.EnableEvents = False
    Set aFound = Range("A3:A18").Find(Range("AC3").Value, , xlValues, xlWhole)
    Set dFound = Range("B2:R2").Find(Range("AC4").Value, , xlValues, xlWhole)
        With Intersect(aFound.EntireRow, dFound.EntireColumn)
            .Value = .Value - Range("AC5").Value
        End With
        Range("AC3:AC5").ClearContents
errorFound:
Application.EnableEvents = True
End Sub

The line in bold clears the data from AC3:AC5 after the change is made to remove the risk of any accidental changes, this can be removed if not required, but should be used with caution if you do.

 

[Patriot2879]

Hi thank you for this code, it doesnt seem t subtract the total from the booked, the whole range is AC3 to AC6 with AC2 being the area, AC4 being the date, AC5 is the total and AC6 is the booked which needs to subtracted from the total, for example it needs to find the area and date to input the total then if I want to subtract I add the number to subtract in AC6 so it should then work out AC5-AC6 then update the number in the corresponding cell between A2:R18

 

[Patriot2879]

I did try updating the code with the additional cell, but still no change, hope you can advise.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aFound As Range, dFound As Range, errorFound
On Error GoTo errorFound
If Not Target.Address(0, 0) = "AC5" Then Exit Sub
Application.EnableEvents = False
    Set aFound = Range("A3:A18").Find(Range("AC3").Value, , xlValues, xlWhole)
    Set dFound = Range("B2:R2").Find(Range("AC4").Value, , xlValues, xlWhole)
        With Intersect(aFound.EntireRow, dFound.EntireColumn)
            .Value = .Value - Range("AC6").Value
        End With
        Range("AC3:AC6").ClearContents
errorFound:
Application.EnableEvents = True
End Sub