drag multiple functions, formula skips cells

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

PS. I attached the beginning of my file the formulas can be found on the tab portfolios

Instead of D5 in your formula, use: OFFSET($D$5,,INT((COLUMNS($D:D)-1)/4),,)
Instead of E5 in your formula, use: OFFSET($E$5,,INT((COLUMNS($D:D)-1)/4),,)
Instead of F5 in your formula, use: OFFSET($F$5,,INT((COLUMNS($D:D)-1)/4),,)
Instead of G5 in your formula, use: OFFSET($G$5,,INT((COLUMNS($D:D)-1)/4),,)

Apologies, you'll have to use ; and not , in the formulae, as I didn't spot that you're in Amsterdam...

Glenn, when I put this in de formula Excel tells me the formula contains an error. the formula is like this:

=INDEX(Table68[Number];MATCH('values 10% hoogste return'!OFFSET($D$5;;INT((COLUMNS($D:D)-1)/4);; );Table5[Dec-91];0))

Is this correct or do I need to change things? I am sorry but I am kind of an Excel noob.


Lars

=INDEX(Table68[Number];MATCH(OFFSET('values 10% hoogste return'!$D$5;;INT((COLUMNS($D:D)-1)/4);; );Table5[Dec-91];0))

if that's not it, please post a sheet. It's always easier to test things when we have a sample sheet...

use this in J10:

=INDEX(Table68[Number];MATCH(OFFSET('values 10% hoogste return'!$H$5;;INT((COLUMNS($J:J)-1)/4);; );Table5[Jan-92];0))

it still gives an error in your sample, as there's no Table 5 in it.

On tab 6 maands cumu is the column numbers table 68 and the other columns are table 5.

Duhhhh. It's early morning here in Thailand and the coffee hasn't worked yet.

=INDEX(Table68[Number];MATCH(OFFSET('values 10% hoogste return'!$H5;;INT((COLUMNS($J:J)-1)/4);; );Table5[Jan-92];0))

It seems to work OK for me.... when there is actually a value in table 5 for the formula to match.

I just selected all the columns to the left of the formula and deleted them. As you can see, it still works fine.