My question:
I have a big data file with 4 formulas. The second, third and fourth formula are linked with the first formula. The first formula is linked to data on different tabs.
the first formula is:
1e formula =INDEX(Table68[Number];MATCH('values 10% hoogste return'!D5;Table5[Dec-91];0))
Here the D5 should go to E5 etc when I drag it the months at the end of the formula should also go one month further. When I drag this it is no problem but I want to drag all 4 formulas at the samen time. But when I do that the D5 becomes H5 but which should become E5. This is because the formula is getting dragged to 4 cells further (because I have 4 formules in a row). The months do change with one step at a time so that part of the function does not give a problem.
So my question is: can I drag all formulas at once? In such way that they change to the good number?
The other formulas are:
2e =VLOOKUP(F10;'6 maands cumulatief'!$A2:$B2645;2;0)
3e =VLOOKUP(F10;'6 maand cumu'!$A2:$JL2645;COLUMN(Table5[Dec-91]);0)
4e =VLOOKUP(F10;Table44;COLUMN(Table44[31/12/1992]);0)
The F10 in the formula refers to the cell before the second formula, so the cell where the first formula is in.
So you could say that the second formule uses the cell before it own cell, the third formula 2 cells before its own cell, and the fourth formula 3 cells before its own.
Does anyone know how to do this? Because I have 300 columns to make if I have to do it per formula I need to make 1200 formulas and insert them by hand, which is going to take a very long time.
Thank you in advance!
Lars
I have a big data file with 4 formulas. The second, third and fourth formula are linked with the first formula. The first formula is linked to data on different tabs.
the first formula is:
1e formula =INDEX(Table68[Number];MATCH('values 10% hoogste return'!D5;Table5[Dec-91];0))
Here the D5 should go to E5 etc when I drag it the months at the end of the formula should also go one month further. When I drag this it is no problem but I want to drag all 4 formulas at the samen time. But when I do that the D5 becomes H5 but which should become E5. This is because the formula is getting dragged to 4 cells further (because I have 4 formules in a row). The months do change with one step at a time so that part of the function does not give a problem.
So my question is: can I drag all formulas at once? In such way that they change to the good number?
The other formulas are:
2e =VLOOKUP(F10;'6 maands cumulatief'!$A2:$B2645;2;0)
3e =VLOOKUP(F10;'6 maand cumu'!$A2:$JL2645;COLUMN(Table5[Dec-91]);0)
4e =VLOOKUP(F10;Table44;COLUMN(Table44[31/12/1992]);0)
The F10 in the formula refers to the cell before the second formula, so the cell where the first formula is in.
So you could say that the second formule uses the cell before it own cell, the third formula 2 cells before its own cell, and the fourth formula 3 cells before its own.
Does anyone know how to do this? Because I have 300 columns to make if I have to do it per formula I need to make 1200 formulas and insert them by hand, which is going to take a very long time.
Thank you in advance!
Lars