row manipulation

**mjj4golf** · 07-01-2013, 12:21 PM

I have a spreadsheet with one row (a1:a1540). I want to print this out but obviously it is a waste of paper to print as is. So I have partial rows taken from col a and put it in different columns at the top from of the page. Then I would have a full page . In other words, in col A a1-a69, then second column c1-c69 using data from a70-a139, e1-e69 using data from a140-a209 etc. etc. I am trying to get the data in the other columns by referencing the cells in column a. So, in cell c4 I would have =a70. However this would be time-consuming and I would like to reference a70 some other way so that I can add 70 to a1 to ref a70. eg =a1+70 would equal a70.

**Pauleyb** · 07-01-2013, 12:42 PM

Since you reference A1:A1540, I assume you mean your data is in a column and not a row. I also think you meant that C1 (not C4) would have =A70...

This is quick and dirty, but it should work. Put this in cell C1, then drag down to C69, then drag across as far as necessary. You will see #REF! errors at the end of your list if there is empty data.

=IF(ISEVEN(COLUMN()),"",INDEX($A$1:$A$1540,ROW()+69*((COLUMN()-1)/2)))

**mjj4golf** · 07-01-2013, 05:21 PM

Thank you for the answer. However, I forgot to mention that I am using of pivot table. So the formula that you gave me will not work. Basically I am just trying to reference other cells by concatenating column and row(A+x) = Ax. Example:
A+25 = A25. So now if I put =A+25 in a cell, I will get what is in cell A25. This is what I am having trouble with.