copying formulas and changing cell references

  • Thread starter Thread starter mainsol
  • Start date Start date
M

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!
 
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
 
Back
Top