Hi, is it possible to make these formulas run into the background through VBA?
In “PPMP I (3)” sheet columns E and F, there are index+match formulas to generate unit and unit costs from the “APP PART A 2022”.
It works. However, there were times that I must replace the formula and manually enter the UNIT and UNIT COST that are not to be found in “APP PART A 2022”.
With this, the formula is erased. What I think is if it is possible to make this formula work in the background without inputting the formula in columns E and F.
If those columns were filled with values/data manually, Index+Match through VBA will not work but if it is left emply, the VBA will work as Index+Match.
Although I’m not familiar with the sheet you are referencing, I believe you are asking to be able to have a column be sometimes manually entered and sometimes from a formula. Excel isn’t very accommodating. I wouldn’t recommend VBA although I see it could work, with VBA you need to have something trigger it to run, usually a button or some event. The beauty of Excel formulas is that they just run when they need to.
A suggestion -add a ‘helper’ column next to the column with the lookup formula. Use this column to enter the occasional manual entry. Change the lookup formula to be something like “IF helper column is not blank, then use it, otherwise do lookup”
Another suggestion - can you add to the lookup table so that the lookup always works and you don’t need any manual override?
