Change how the formula varies with the cell index

[samir_aguiar]

It was hard to try to explain this in the title!

You know when you type a formula, say SUM(A1:A20) and when you drag to other cells it changes to SUM(B1:B20), SUM(C1:C20), etc.? So, I wanted that to happen when cells are not close to each other, neither in the same row or column.

I have this formula:
=LEFT(E13;FIND("/";E13)-1)

I wanted to give a name to it, say test, to make it easier to type. Then, I wanted to use this formula (test) in cells O4:O9, where O4 uses E13, O5 uses H13, O6 uses K13, O7 uses E26 and so on. How do I do that??

thanks a lot.

 

[samir_aguiar]

Sorry, I guess I meant reference instead of index!

 

[markmzz]

Samir,

You have to create two names.

TestCell =OFFSET('Plan1'!$A$1,INT((LIN()-4)/3)*13+12,(LIN()-4)*3+4-(INT((LIN()-4)/3)*9))

Test =LEFT(TestCell,FIND("/",TestCell)-1)

Then, use the name Test in cells O4:O9.

Markmzz

 

[samir_aguiar]

Took a while for me to understand the formula but I got it.
Thanks a lot!

 

[markmzz]

Took a while for me to understand the formula but I got it.
Thanks a lot!

Samir_aguiar,

Thanks for the feedback.

Markmzz