Forum Discussion
Formulas automatic fill
It is actually quite simple. However, you have to perform an intermediate step for a workaround.
First create all the necessary formulas. Write the function: =C$117-C$113 in any cell. Make sure you have space to the right of the cell. Now extend the formula to the right. Click with the mouse on the point at the bottom right of the cell and then drag to the right over the next cells as far as needed. Then select the whole cell area and copy it. Paste at the desired position with the option Paste Transpose. You have already created your formulas. Now you can delete the auxiliary cells. Good luck.
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
- I apologize; I did not mean to be rude. And there might be a misunderstanding here. I meant to say the workaround with the TRANSPOSE function seemed too laborious for a long table.
Best regards.