CODEWITHSUNDEEP
excel
Đức Thanh Nguyễn
Đức Thanh Nguyễn

Reputation: 9365

Excel automagically insert formula

I came across this excel files (xlsx) so there is no VBA involved, no table, no name. But columns C is updated with formula "automagically" when I insert a number into column B. I checked the name manager, there is no name. I would like to know how can I achieve this kind of functionality. Have anyone come across something like this?

P.S: When I copy Range A2:C13 and paste to a different files. The magic still works!

Screenshot in Excel 2016

enter image description here

Upvotes: 1

Views: 70

Answers (3)

J. Chomel
J. Chomel

Reputation: 8393

I manage to have this behavior by having the formula defined on all the cells of the column* with an "IF(NOT(ISBLANK(c); <your formula>; "")" condition on the input cell:

  • e.g. you enter this in C1

    =IF(NOT(ISBLANK(B1)); B1/(1+B1);"")

  • you click and drag the formula like you know how;

  • Et voila

*)(or as many as necessary, since it could be a little "heavy" for Excel to have the formula 1048576 times, nearly for nothing)

Hope it helps

Upvotes: 0

Đức Thanh Nguyễn
Đức Thanh Nguyễn

Reputation: 9365

Ok, I got this one. Simply add 4 or 5 subsequence row with formula in column B and C. And continue continue to enter data in column B like this GIF:

enter image description here

Upvotes: 1

zipa
zipa

Reputation: 27889

So, you go to File>Options>Advanced and tick the box that says:

Extend data range formats and formul̲as

Upvotes: 1

Related Questions