Multiple formulas in column with reference to single cell - Copy Issue

[MarVil85]

Hi guys/gals,

I have a problem with which I hope someone could help me. If the title of this thread is not clear enough, I apologise in advance. I've spent hours on Google trying to find some info on the matter, but my biggest problem seems posing the question correctly.
First, some details:

I created this workbook in order to create a csv file which will in turn become a shapefile indicating occurrences of species within our country.

On sheet one, C3, I enter the QDS(Quarter Degree Square) code, e.g.: 2331CA. In cells J3:N3 the coordinates(decimal) are given for the specific QDS's polygon points.
Now, on sheet 2 I have the following:

A2 : =IF('Calculation Sheet'!$C3="","",CONCATENATE('Calculation Sheet'!$J3,";",'Calculation Sheet'!$B3,";",'Calculation Sheet'!$C3))
A3 : =IF('Calculation Sheet'!$C3="","",CONCATENATE('Calculation Sheet'!$K3,";",'Calculation Sheet'!$B3,";",0))
A4 : =IF('Calculation Sheet'!$C3="","",CONCATENATE('Calculation Sheet'!$L3,";",'Calculation Sheet'!$B3,";",0))
A5 : =IF('Calculation Sheet'!$C3="","",CONCATENATE('Calculation Sheet'!$M3,";",'Calculation Sheet'!$B3,";",0))
A6 : =IF('Calculation Sheet'!$C3="","",CONCATENATE('Calculation Sheet'!$N3,";",'Calculation Sheet'!$B3,";",0))

As you can see, all 5 of the rows references to cells in one row. now, if I were to drag/copy the above mentioned formulas down the column, the next set will change all the row numbers to 7.
This is where my problem lies, I want the next "set" of formulas to reference to the next row on sheet one, e.g. C4...and as I continue down the column, each "set" of formulas must only increment the reference cells' row numbers by one.

I have tried using the offset formula, but just couldn't get my head wrapped around it.

Cheers for now...

MarVil

[martindwilson]

replace references to c3 b3 etc with the index
INDEX(C:C,CEILING(ROW(A11),5)/5)
eg
=IF(INDEX('calculation sheet'!C:C,CEILING(ROW(A11),5)/5)="","",CONCATENATE(INDEX(j:j,CEILING(ROW(A11),5)/5),";",INDEX(b:b,CEILING(ROW(A11),5)/5),";",INDEX(C:C,CEILING(ROW(A11),5)/5)))

[MarVil85]

Ahh...great stuff!!! Thanks a lot martindwilson!
Wow, that was fast, I didn't expect an answer this soon.

I will definitely use this forum again.

Cheers for now.

MarVil