Dynamic Macro for INDEX/MATCH Formula

uporabnik

New Member
Joined
Aug 9, 2024
Messages
1
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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:

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

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

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

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top