Copying formula changing references

[nmcbs84]

Hi everyone,

I dont know if it is possible or if the question makes sense but here it goes.

Lets assume i have a column/line with the following values/formulas

E

8 ='1'!L15+'1'!L138
9 ='2'!L15+'2'!L138
10 ='3'!L15+'3'!L138

As you can see im catching those references from different sheets

Now what i want is copy those formulas to the next column (F) but changing the references for the following:

F

8 ='1'!O15+'1'!O138
9 ='2'!O15+'2'!O138
10 ='3'!O15+'3'!O138

Does it make sense? Is it possible without have to go to each cell changing the references manually? As you can see i only changed the reference from L to O. The rest maintains the same.

Thank you

[Special-K]

UNTESTED!!

Change the formula in E8 to

=INDEX('1!'!L15:ZZ15,1,(COLUMN()-5)*3+1,)+INDEX('1!'!L138:ZZ138,1,(COLUMN()-5)*3+1)

Now you should be ok to copy across from F to G to H etc for as many columns as far as column ZZ

UPDATE: Mm, still means you'll have to change the sheet number for rows 9 and 10 :-(
Though you've only suggested three rows so that shouldnt be too hard to do.

[Special-K]

UNTESTED!!

Or change ALL the formulas to

=INDEX(INDIRECT("'"&ROW()-7&"'!"&"L15:ZZ15"),1,(COLUMN()-5)*3+1,)+INDEX(INDIRECT("'"&ROW()-7&"'!"&"L138:ZZ138"),1,(COLUMN()-5)*3+1)

then you should be able to copy it down for as many rows as you have sheets

[KOKOSEK]

If you can create macro in your sheet

mini macro:

Please Login or Register  to view this content.

then in col F

Please Login or Register  to view this content.

[nmcbs84]

Special-K actually it is not 3 rows only, it is 31 rows and 12 columns :-)
Anyway im a fairly begginer in Excel im going to try to digest the info and see if i can do it.
I would post the excel file here but it is a bit large, in Portuguese (even if math formulas are rather universal, and thats what matters i suppose here).

[nmcbs84]

Well what the heck here it goes the excel file. If you have any doubt understanding portuguese please tell me. The tab im working with is the one that says "Evolução Receitas Por dia".
If you can help. Thank you very much

[Special-K]

Special-K actually it is not 3 rows only, it is 31 rows and 12 columns :-)
Anyway im a fairly begginer in Excel im going to try to digest the info and see if i can do it.
I would post the excel file here but it is a bit large, in Portuguese (even if math formulas are rather universal, and thats what matters i suppose here).

That file refers to coordinates that are completely different to the example you gave and contain breaks within the original sheets 1, 2, 3 etc
This is now more complex than originally described and I don't have that amount of time to spend on this.