Quick Excel formula Q.

[SimoninEaston]

In a spreadsheet I want to reference other cells in blocks of 4 rows - see below.

How do I copy the references, so that I don't have to type them all in by hand? If I try a simple copy/paste it does this...

[LosOjos]

Difficult to see what you're aiming for, but at a guess you need to lock the references. To do so, you put a $ in front of the element you want to lock (row, column or both).

 

For instance, if you wanted to reference A1, and you always wanted to reference that exact cell no matter where the target cell was copied to, you'd change it to $A$1

[LosOjos]

Actually, I see what you're getting at now. The only way I can think of to do that in Excel is to develop an algorithm to generate the cell reference on the fly.

 

Given your example, the following would do it:

 

=ADDRESS(ROUND((ROW()+1)/4,0),IF(MOD(ROW(),4)=0,4,MOD(ROW(),4)),1,TRUE,"text")

 

If you wanted the value of the referenced cell (as opposed to the cell reference itself), wrap it in an INDIRECT i.e.

 

=INDIRECT(ADDRESS(ROUND((ROW()+1)/4,0),IF(MOD(ROW(),4)=0,4,MOD(ROW(),4)),1,TRUE,"text"))

Edited November 18, 2014 by LosOjos

[SimoninEaston]

Yikes! interesting... I'll give that a try when I have a spare mo' - I've tried the Index function which still needs manual edits but less of them!

Cheers for the idea!