Formulas automatic fill

ridimarcar 

In principle, everyone is allowed to do what they want. Which solution you prefer is not really important for the community. The main thing is that you were helped.

But what is not so great is that you reject a working solution because, according to you, it doesn't work for your worksheet because it is too big. The statement is false and misleading. Other readers of this article might believe it and not use a simple working solution because you claim it is not suitable.

Your task to the community was to subtract values in columns and write the formulas one below the other. An Excel worksheet has 16384 columns and 1048576 rows. Even if you have all the columns of the spreadsheet in use, your spreadsheet cannot possibly be too large for the type of formula creation I suggested. I assume that I explained the workaround in too complicated way and that you could not understand it.

Two more notes on the alternative.

INDIRCT() is a volatile function. Microsoft recommends using volatile functions sparingly, as Excel will re-evaluate them every time a new calculation is made, even if none of the arguments change. This will slow down the recalculation times of the worksheet.

So especially if you have a large worksheet with many calculations it would be advisable not to use INDIRECT().

If you have to use INDIRECT(), then please use it a bit smarter. The function already brings the R1C1 format with it, so there is no need for an additional Address() function and also no additional subraction if you reference the right row straight away.

=INDIRECT("R117C"&ROW(A3),0)-INDIRECT("R113C"&ROW(A3),0)

Please don't misunderstand. I was just too irritated by your answer and had to get it off my chest.

With kind regards