Regulating auto fill-in function

Hello everyone,

I'm kind of new with excel and I'm turning crazy with a small problem.

I need to perform some operations with about 1000 daily data contained data-cells.
So I picked a column, inserted my formula in the first cell, and then dragged it down for the one thousand following cells.
Now, I hoped excel would understand automatically the right range of data-cells to be considered in these result-cells but obviously that would have been too good...
In practice, the formula I inserted in the first cell was:
=MATR.TRASPOSTA(MATR.TRASPOSTA(T309:V309)+MATR.PRODOTTO(MATR.PRODOTTO(MATR.PRODOTTO(BA309:BC311;MATR.TRASPOSTA(P320:R324));BQ309:BU313);MATR.TRASPOSTA(AI309:AM309)))

therefore in the cell right underneath I wanted to have:
=MATR.TRASPOSTA(MATR.TRASPOSTA(T310:V310)+MATR.PRODOTTO(MATR.PRODOTTO(MATR.PRODOTTO(BA312:BC314;MATR.TRASPOSTA(P325:R329));BQ314:BU318);MATR.TRASPOSTA(AI310:AM310)))
and so on.

As I said, excel, after laughing at me, gave me:
=MATR.TRASPOSTA(MATR.TRASPOSTA(T310:V310)+MATR.PRODOTTO(MATR.PRODOTTO(MATR.PRODOTTO(BA310:BC312;MATR.TRASPOSTA(P321:R325));(BQ310:BU314));MATR.TRASPOSTA(AI310:AM310)))

My question is if it is possible to solve this small problem without manually changing the formulas or if, in any case, it is possible to tell excel how to perform copy&paste rules (for example, how do I create a column with cells:
=5+A1; =5+A6; =5+A11; =5+A16 etc.).

I'm not sure if this is an easy or tough problem but I'll love forever anyone who can save me.

Thanks
Fra

Hello, and welcome to the forum

To make the cell references change another way than what Excel think you want it to change, there are some tricks that could involve the use of the functions OFFSET, ROW, COLUMN, INDIRECT and MOD.

I see that you (probably) use an Excel version with another language than English, so these functions probably have different names.

I will suggest that you upload an Example file (you have to put it in a .zip file).

Most people on this forum uses English formulas, when we open your uploaded file the formulas gets translated. We can the edit your formula and upload the edited file. When you open this file it gets translated back to your language, so you can see the formulas in your language.

You dont have to upload your complete file, just a sample with 4-5 examples on how you want your references to change, or some examples on what results you want from the formulas.

Hey, thanks a lot for the reply.
You are right, I'm indeed Italian, with an Italian language excel version.

In any case, I followed your advice and attached a file.
In the array B3, C3, D3 there is the proper matrix formula. In the following three rows (B,C,D4; B,C,D5; B,C,D6) I added manually the correct updated formulas so it should be easy to understand what pattern I'm trying to create for those three columns.

I've no idea how to achieve that, any help would be just great!
Bye
Fra

I must admit that you formulas was a bit heavy for me to work on....
but I think I know what you are looking for, and will give you an example file, so maybe you will be able to implement this in your formulas.

You need the reference to change by X number of row for each row you copy the formula down, instead of 1 row as is the defalt when copying relative references.

This is the formula i use to replace the reference A1

=OFFSET($A$1,(ROW()-Y)*X,0,1,1)

Open the attachment to get the formula with Italian formula names.

X is the number of rows to increment the reference with for each row you copy down.

Y is the row number where you put your first formula.

In your original formula (in row 2) you have a reference to the range:
BA309:BC311
In the formula you copy to the next row you want this to change to:
BA312:BC314
and in the next row after that to change to
BA315:BC317

Replace BA309:BC311 in your original formula with:
OFFSET($BA$309,(ROW()-2)*3,0,3,3)

The last 2 parameters og the OFFSET function gives you the size of your range. In this case a 3x3 cell range.

And of course you have to translate the names of OFFSET and ROW to Italian, but you will find these names in my attachment.

Hope this helps, and feel free to ask if you need more assistance.

I would realy like to meet the idiot at Microsoft that came up with the "good" idea of translating formula names to the local language...... but I don't think he would like to meet me......