Dragging multiple formulas

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 sort of get the idea of what your trying to do, but to make a suggestion that will definitely work, I would need to build a dataset that replicated the problem you describe, and I bet I'd not be able to do that.
Without a sample workbook, I would suggest creating a macro to do it for you. I presume that if you were doing it manually you would (a) drag the first 4 formula (individually) to their new locations and (b) move to the start of the second set of 4 and (c) go back to (a)
Repeating this until you have done them all. You have here a FOR/NEXT loop, ideal for a macro, with the caveat that your data is laid out consistently. THis is so that a repeat of the actions to complete the first 4 entries will work for all.
If so, then do (a) (b) and (c) as defined above just once, while your running the macro recorder. You then need to add the FOR and NEXT coding to complete the loop. If you post the recoded code with the number of times it needs to repeat I or another helper will be able to assist you.
If you are able to try on your own:
1) Try doing 2 or 3 first to make sure it works OK.
2) Don't allow your original worksheet to be overwritten, deleted etc until your sure that your update has been fully validated/verified.

Good Luck

Hercules1946 thank you for replying, I am kind of a noob when it comes to Excel.

I do not really what you mean for the for next loop function, I will look this up on the internet. My problem is that I want to drag 4 functions at once but the first function has D5 (a cell from an other tab) in the formule which has to change to E5 but because the function its position move up 4 places (because there are 3 formulas behind the first one in the same row) it skips to H5 and not E5.

I attached a file to this message to make is clearer. It is about the tab portfolios.


Lars.

View attachment DATA - VOORBEELD.xlsx

Hercules1946 said:

I sort of get the idea of what your trying to do, but to make a suggestion that will definitely work, I would need to build a dataset that replicated the problem you describe, and I bet I'd not be able to do that.
Without a sample workbook, I would suggest creating a macro to do it for you. I presume that if you were doing it manually you would (a) drag the first 4 formula (individually) to their new locations and (b) move to the start of the second set of 4 and (c) go back to (a)
Repeating this until you have done them all. You have here a FOR/NEXT loop, ideal for a macro, with the caveat that your data is laid out consistently. THis is so that a repeat of the actions to complete the first 4 entries will work for all.
If so, then do (a) (b) and (c) as defined above just once, while your running the macro recorder. You then need to add the FOR and NEXT coding to complete the loop. If you post the recoded code with the number of times it needs to repeat I or another helper will be able to assist you.
If you are able to try on your own:
1) Try doing 2 or 3 first to make sure it works OK.
2) Don't allow your original worksheet to be overwritten, deleted etc until your sure that your update has been fully validated/verified.

Good Luck

Click to expand...

I uploaded a file, see my other post

Nobody?

larsoe said:

Nobody?

Click to expand...

Never it is solved!

larsoe said:

Never it is solved!

Click to expand...

Does this mean you have resolved your issue?

larsoe said:

it skips to H5 and not E5.

Lars.

Click to expand...

You may have solved the problem with E5 and H5, but I took a look anyway .
In 'portfolios' your wanting to drag the formula across 4 columns (F to J) whilst only adjusting the cell addresses in the formulae by one column (D to E) and this can't be done. The good news is you don't need to calculate the row number (again) in J10 because you have already got this in F10. When I adjusted for this and the date column in J10 this fixed all 4 and I was able to copy down. Not sure about the stuff lower down, but Im assuming that its part of your problem solving.

See my attachment.