Create formula dynamically without VBA

Question

For example, I have input data in column A and some complicated formula in Column B as a function of data in A. How many rows of data in A depends on user input. It can be anywhere from 2 to 100,000. Traditionally I will populate all 100,000 rows of Column B with the same formula. But that introduces a huge overhead of unnecessary formula evaluation if only two rows of column A are used.

If VBA is allowed, it can be as easy as write a 'For' loop. But I want to know if this is possible without VBA.

Is it possible to dynamically add/remove the formula in column B with worksheet function only?

Where/How are you populating the formula? You could use CountIf to see how many values are actually populated: support.office.microsoft.com/en-gb/article/… — user1274820
– user1274820, Commented Feb 2, 2015 at 20:43
you will have to use VBA as there is no formula to populate a formula in a cell. — Sorceri
– Sorceri, Commented Feb 2, 2015 at 20:44
Without VBA you are going to have to have a formula in each cell. Now that being said, if you want to cut down on the calculation time it is plausible that you could make a formula that expanded a whole column and still was time efficient in calculation. — chancea
– chancea, Commented Feb 2, 2015 at 20:53
Looks like vba is inevitable for a dynamic formula adding/removing. I just try to avoid any expensive formula evaluation on the calculation chain if the input is null. Maybe a simple if logic to test the presence of input before any expensive formula would be a good idea. — Peter
– Peter, Commented Feb 3, 2015 at 14:08

L42 · Accepted Answer · 2017-07-26 03:52:56Z

1

Put the data and formulas inside a Table so that each time you tab down to the next row, the formula column will automatically size with the table and populate the formula along with it.

edited Jul 26, 2017 at 3:52

L42

19.8k11 gold badges47 silver badges70 bronze badges

answered Jul 26, 2017 at 1:39

Josh Anstead

3482 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create formula dynamically without VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related