Update several cells based on entry

Hello again

pls take a look at these two sheets


how can I change the contents of one cell or a range of cells in a column based on
two cells ( here i.e. : ldate & Type) & update the found range of cells with a certain amount???

(all the cells in the column Amt will change if we have in sheet 1 the type A & date < 1/1/2003 - of course we have a link in the nb because we have 2 sheets)

Thank you

TO change the sheet2 column B values (the actual values in the cells) would require VBA.

To create a column C with new values based on the values in column B and the test you want to do could be done with a formula.

What do you want to happen to column B values? What's the logic for changing them?

Originally Posted by JBeaucaire

TWhat do you want to happen to column B values? What's the logic for changing them?

Explain what you want to happen, pick a row in your sample workbook and tell us what happens to the column B value...then I can suggest a formula that does that.

This isn't the programming forum, but your question clearly states "how to change the value in the original cell based on two matching criteria".

To change the actual value in the original cells automatically requires a macro, VBA. This macro does that:

Option Explicit

Sub AmtAdjuster()
Dim ws1 As Worksheet    'sheet with controlling values
Dim ws2 As Worksheet    'data sheet to adjust
Dim RNG As Range

Set ws1 = Sheets("Sheet1")
Set ws2 = ActiveSheet       'run the macro from the sheet to adjust

If ws2.Name = ws1.Name Then
    MsgBox "Please activate the data sheet to adjust before running the macro." _
        & vbLf & "Aborting..."
    Exit Sub
End If

With ws2
    On Error Resume Next
    Set RNG = .Range("B:B").SpecialCells(xlConstants, xlNumbers)
    
    If Err.Number > 0 Then
        MsgBox "No numbers where found in column B." & vbLf & "Please check data layout and try again." _
            & vbLf & vbLf & "(Nb in column A, Amt in column B)"
        Exit Sub
    End If
    
    RNG.Offset(, 24).FormulaR1C1 = _
        "=IF(AND(INDEX(Sheet1!C4, MATCH(RC1, Sheet1!C1, 0))=Sheet1!R4C7,INDEX(Sheet1!C3, MATCH(RC1, Sheet1!C1, 0))<Sheet1!R4C8), Sheet1!R4C10, RC2)"
    RNG.Value = RNG.Offset(, 24).Value
    RNG.Offset(, 24).ClearContents
    MsgBox "Done"
End With
    
End Sub

I've attached your sheet with the macro already installed, you need only put your values in columns A:B and click the button to run the adjuster.

The macro is actually using a worksheet formula to decide which values to change, putting that formula into column AA, then copying the resulting values over the original values in column B.

The formula being used is this...place it in D3 and copy down to see it work:

=IF(AND(INDEX(Sheet1!$D:$D, MATCH('Sheet1 (2)'!$A3, Sheet1!$A:$A, 0))=Sheet1!$G$4,INDEX(Sheet1!$C:$C, MATCH('Sheet1 (2)'!$A3, Sheet1!$A:$A, 0))<Sheet1!$H$4), Sheet1!$J$4, $B3)

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Press F1 in Excel and read up on the INDEX() function. It should include examples of using MATCH() to resolve the 2nd and or 3rd parameters, which is how I typically use it.

After you've read the help files, come back with any followup questions you may have.