formula drag down/copy changing integers of 6

[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)

 

[Roger Govier]

Hi

=IF(INDEX('EAST Region PSR Detail '!A:A,9+(Row(A1)-1)*6)=""," ",
INDEX('EAST Region PSR Detail '!A:A,9+(Row(A1)-1)*6))

 

[Max]

This is one way to get it going

In the start cell, where you have:

=IF('EAST Region PSR Detail '!A9=""," ",'EAST Region PSR Detail '!A9)

Replace it with this:
=IF(INDIRECT("'EAST Region PSR Detail
'!A"&ROWS($1:1)*6+3)="","",INDIRECT("'EAST Region PSR Detail
'!A"&ROWS($1:1)*6+3))
Copy down to return desired results

I took the liberty to amend the return to a null string: "", which imo,
poses less potential downstream problems than to return a single space: " "
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---