Forum Discussion
Formulas automatic fill
In an Excel spreadsheet, I have in one cell the following formula, = C$117 - C$113; in the row below, the formula is, = D$117 - D$113. I want the letters in the formula to be updated as I drag the cells DOWN. So, the next row should be, = E$117 - E$113, and so on. However, the formula is not updating. Can anyone tell me please how can I do it? It will be a lot of work to enter the formula manually in each row. Thanks.
As variant, if first formula is in A113, that could be like
=INDEX($C:$Z,17, ROW()-ROW($A$112)) - INDEX($C:$Z,13, ROW()-ROW($A$112))- Many thanks! I will try that.
- I will try that. Thanks very much!
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.
- Thanks very much for the answer. For rather short tables, this workaround is good. Mine is a bit too long. Thanks anyway!
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
=INDIRECT(ADDRESS(117,ROW()-110))-INDIRECT(ADDRESS(113,ROW()-110))You can try this forrmula which is in cell A113 and filled down.
