copying formulas and changing cell references

[mainsol]

Good Morning!

I am wondering how to copy a cell and paste it so that the cell reference
within the formula changes. For example:

=IF('EAST Region PSR Detail '!A9=""," ",'EAST Region PSR Detail '!A9)
when dragged down becomes
=IF('EAST Region PSR Detail '!A10=""," ",'EAST Region PSR Detail '!A10)

What I need to accomplish is to have the cell reference within the formula
to change by 6 integers vs 1, so the correct formula would be
=IF('EAST Region PSR Detail '!A15=""," ",'EAST Region PSR Detail '!A15)

Any suggestions would be most appreciated!

 

[Max]

See response in your other post
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

 

[Pete_UK]

Try this:

=IF(INDIRECT("'EAST Region PSR Detail '!A"&(ROW(A1)*6+3))="","
",INDIRECT("'EAST Region PSR Detail '!A"&(ROW(A1)*6+3)))

then copy this down.

The term ROW(A1)*6+3 evaluates to 1*6+3 (=9), but when copied down
this becomes ROW(A2)*6+3 (=2*6+3, =15), then ROW(A3)*6+3 (=3*6+3,
=21), and so on. This is then concatenated to the rest of your
reference, and INDIRECT allows you to "calculate" references in this
way.

Hope this helps.

Pete

 

[Pete_UK]

Ah! I didn't see that you had posted elsewhere - please do not multi-
post.

Pete