Change cell reference within formula

[Hytten]

Hi

I have several worsksheets, which all basically are alike. However, there are small variations in row number for the same lines in the two sheets.

I have an overview sheets, which are meant to sum up, what the different sheets amount to. Therefore i have created formulas like the one below

=IF(Sheet1!$K$47=0;0;IF(Sheet1!$K$50=1;IF(Sheet1!$K$51=1;IF(Sheet1!$K$52=1;IF(Sheet1!$K$53=1;IF(Sheet1!$K$54=1;IF(Sheet1!$K$55=1;IF(Sheet1!$K$56=1;IF(Sheet1!$K$57=1;"done";Sheet1!$C$57);Sheet1!$C$56);Sheet1!$C$55);Sheet1!$C$54);Sheet1!$C$53);Sheet1!$C$52);Sheet1!$C$51);Sheet1!$C$50))

(all it does is look to see if a job i 100 % complete, if it is it moves to the next, when all are done it says "done" if one isn't done, it displays the job that is next in line. (There are probably more clever ways to do this, but I couldn't come up with any))

This all works fine

BUT

when i want to copy the said formula once again, to calculate the same for sheet2, the same formula doesn't work, as the references are a couple of rows of.

Therefore I ask, if someone knows of a clever way to "add 5 rows to a cell reference, so EG K147 becomes 152, while this happens to all the other cell references?

There are around 64 of these for each sheet, which there are 10 of, so it would be a bummer to have to do it all manually.

Hope someone can help and thank you

btw: I searched google extensively, but my internet skills failed me.

 

[Hytten]

In other words, i would like to be able to apply the OFFSET function to all the cell references inside the formulas of all the cells in an array, like a makro doing something like this

OFFSET("all the cell references in this array";-5,0)

Thanks in advance

 

[Hytten]

So i didn't figure it out - but i made a work around.

I added lines in various places and hid them. In this way all sheets became aligned and I could use the same formula for all sheets.