Conditional product using a fixed row on one sheet, varying row on another?

[eng442]

I have two spreadsheets like this:

Purchases

+---+----------+-------+------------+---------------------+
|   | A        | B     | C          | D                   |
+---+----------+-------+------------+---------------------+
| 1 | Date     | Asset | Avg. price | Adjusted avg. price |
+---+----------+-------+------------+---------------------+
| 2 | 06/26/19 | ABCD1 | 60         | 10                  |
+---+----------+-------+------------+---------------------+
| 3 | 07/29/19 | WXYZ1 | 60         | 60                  |
+---+----------+-------+------------+---------------------+
| 4 | 08/29/19 | ABCD1 | 60         | 60                  |
+---+----------+-------+------------+---------------------+

Events

+---+----------+---------------+-------+------------+
|   | A        | B             | C     | D          |
+---+----------+---------------+-------+------------+
| 1 | Date     | Event         | Asset | Multiplier |
+---+----------+---------------+-------+------------+
| 2 | 06/26/19 | Reverse split | ABCD1 | 6          |
+---+----------+---------------+-------+------------+

I want Adjusted avg. price to be Avg. price x 1/Multiplier if Asset is the same on both columns and if Events.Date >= Purchases.Date and Event = "Reverse split".

I am trying to do this one step at a time. As far as matching the asset goes I used the following formula for column D of the purchases sheet:

IF($Events.C2=B2, 1/$Events.D2*C2, C2)

Then I selected the row and pulled down the mouse to apply the formula to all rows in the column. As can be seen in the purchases sheet above, this works for row 2, but does not work for row 4, as the formula becomes

IF($Events.C4=B4, 1/$Events.D4 * C4, C4)

I also tried using PRODUCT(IF($Events.C:C=B:B, 1/$Events.D:D, 1), C:C) or something of the sort (I lost the formula), but the result was the same.

I can see two easy workarounds:

1. Filling in the formula manually for each row
2. Having one sheet for each event with as many rows as the purchases sheet

However, this does not scale, as I expect hundreds of rows on both sheets. Is there any way I can do this using Calc’s formulas, or should I go for a macro or just abandon Calc altogether and use a programming language and a database?

[mariosv]

I think the Absolute&Relataive references should help about how to manage that formulas

[schiavinatto]

The Events worksheet only has one item, and everyone has to compare with it, is that it?

If YES use the formula: =IF($Events$C$2=B2,1/$Events.$D$2*C2,C2), so C2 and D2 of Events are fixed.

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.