Help Please For Formula Copy Skipping Cells?

Hi,

I'm a bit stuck, there must be an easier way to do this, have a formula as follows:
=IF('Full Survey'!C31="","",'Full Survey'!C31)

It refers to another tab which contains data I want to retrieve and show on a summary but the data is every 27 rows so it's like this...
B3 =IF('Full Survey'!C31="","",'Full Survey'!C31)
B4 =IF('Full Survey'!C58="","",'Full Survey'!C58)
B5 =IF('Full Survey'!C85="","",'Full Survey'!C85)
B6 =IF('Full Survey'!C112="","",'Full Survey'!C112)

etc etc for 80 rows

I need to do this 80 times and I've been manually amending it as copy or dragging it down doesn't continue the pattern...any ideas please?

Many Thanks!

Originally Posted by FDibbins

See if this will get tou started...

=INDIRECT("'Full Survey'!C"&ROW(A1)*27)

(you may need to fiddle with ROW(A1)

Thanks...not sure what it relates to though? is A1 relating to B3 or the Full Survey Tab?

Originally Posted by Tony Valko

What is the 1st cell that holds the formula?

We can use that cell as the "counter".

B3 is the first cell containing the formula

Originally Posted by FDibbins

ROW(A1) is just a counter, it is not actually referencing anything...

ROW(A1)=1
ROW(A2)=2
etc
so...
1*27
2*27
etc

Thanks, i'll try it again and let you know. Should this ten replace my IF function?

This formula works but not sure what the next should be

=IF(INDIRECT("'Full Survey'!B"&ROWS(B3:B3)*31)="","",INDIRECT("'Full Survey'!B"&ROWS(B3:B3)*31))

It looks like the *31 relates to the row of the full survey page?
So for the formula to work the first line of summary needs to be *4 then the second row needs to be *31 as it's 27 rows further down. This makes the copy down relate to the same cell as the previous row, so although the range expands (B$3:B4) the reference is picking up the same cell i.e. cell 31.

That is the interval but the *31 number remains the same and it doesnt evaluate the 27 row jump.

I've attached a sample file, it's lost some formatting but I hope you can see what I'm trying to achieve on the summary page.

Thanks for your help with this!

the row interval is 27, but the data to be returned starts on row 4, so it should look at rows 4 then + 27 to row 31. But if *27 is entered it doesnt work, the formula needs to be *31 to work but it doesnt copy down it just returns the same value and not the next one in the sequence.

This one works but returns the value in cell B31
=IF(INDIRECT("'Full Survey'!B"&ROWS(B$3:B3)*31)="","",INDIRECT("'Full Survey'!B"&ROWS(B$3:B3)*31))

This returns cell B27
=IF(INDIRECT("'Full Survey'!B"&ROWS(B$3:B3)*27)="","",INDIRECT("'Full Survey'!B"&ROWS(B$3:B3)*27))

so for it to work across all 80 rows it would need to change the *31 or *27 figure each time it is copied down, but it doesnt, its just the range that changes the B$3:B3 changes to B$3:B4 etc which appears to relate to the Summary Page and not the Full Survey page...

this it .....???

=IF(INDIRECT("'Full Survey'!B"&ROWS($B$3:B6)*27+4)="","",INDIRECT("'Full Survey'!B"&ROWS($B$3:B6)*27+4))

Something like this?

=IF(AND((OFFSET('Full Survey'!D$1,(ROWS(H$3:H3)-1)*27,0)="SINGLE")),(OFFSET('Full Survey'!H$14,(ROWS(H$3:H3)-1)*27,0)="IMPROVE")),1,"")

Sorted I think?

=IF(AND((OFFSET('Full Survey'!D$1,(ROWS(H$3:H3)-1)*27,0)="SINGLE"),(OFFSET('Full Survey'!H$14,(ROWS(H$3:H3)-1)*27,0)="IMPROVE")),1,"")

Originally Posted by Tony Valko

Yeah, that'll work but you can use less sets of ( )...

=IF(AND(OFFSET('Full Survey'!D$1,(ROWS(H$3:H3)-1)*27,0)="SINGLE",OFFSET('Full Survey'!H$14,(ROWS(H$3:H3)-1)*27,0)="IMPROVE"),1,"")

Thank you!

Thanks for your help.