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:

Please Login or Register  to view this content.

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.