Dynamic Macro for INDEX/MATCH Formula

[uporabnik]

Hello,

I have a certain challenge, how to use a macro to make a certain formula apply in certain fields (example: fields between C5:G40 => "=INDEX(log!$D:$D;MATCH(1;(log!$E:$E=List1!$B13)*(log!$F:$F=List1!H$3)*(log!$C:$C="H13"))").

In cells (C5:G40) I keep track of the input of certain letters and if this is the case, I write it down with certain arguments in the 'log' sheet. Then, using the above function, I read this value if it exists.

The challenge is how to fit a formula into these cells that will be "dynamic" depending on where it is located. Let's say the formula is in cell C5, the formula combined with INDEX and MATCH then needs to be bound to cell C3 and B5,... if the formula is in cell D9, then it must be bound to cell D3 and B9.

As a final solution (an upgrade of the previous one), I would need that in case I write a value in the "log" sheet in the cell where this "dynamic formula" is set via a macro, and then I override the previous formula (INDEX/MATCH) again in this entered cell. In short, this means that practically "always" this formula would be valid in certain cells. In case a value is found in the log sheet it is printed out, but otherwise the formula with empty value or "" is still valid.


ps. In the cells where the formula is found, I accept exactly defined characters (statuses), so it is important that the value is written with the arguments and read with the formula. In row 3 are the dates that are changing and in column "B" are the products.


Thanks for help or any other guidance.

 

[StephenCrump]

Welcome to the Forum!

Sorry that you haven't had any replies. This is probably because your question is not at all clear.

Let's say the formula is in cell C5, the formula combined with INDEX and MATCH then needs to be bound to cell C3 and B5,... if the formula is in cell D9, then it must be bound to cell D3 and B9.

Here's a simple formula to illustrate how to set absolute references to row 3 and column B:

Range("C5:G40").Formula = "=C$3*$B5"

But it's not clear how this part of your question relates to your original formula?

"=INDEX(log!$D:$D;MATCH(1;(log!$E:$E=List1!$B13)*(log!$F:$F=List1!H$3)*(log!$C:$C="H13"))"