Incrementing cell references slower than by 1

[Rich]

I've transposed the information from one worksheet to a second worksheet in
order to see the information linearly. The cells are referenced directly,
but in the original document, everything is in the same line.

Basically, I want all the cells in rows 2-8 of the new worksheet to be
referenced to row 2 of the old worksheet; rows 9-15 of the new to be
referenced to row 3 of the old, etc. When I copy/paste, instead of using row
3 (of the old), it uses row 9. Same goes when I try to use row 4, it uses
row 16. Is there any way to preserve that?

If more clarification is needed, I can do so, or even provide a link to the
file of what I'm trying to do.

 

[Shane Devenshire]

Hi,

What does your data look like on sheet1 and what do you want it to look like
on sheet2. Normally we use 1. Paste Special, Transpose, 2. The TRANSPOSE
function, 3. The OFFSET function, the INDIRECT, or INDEX functions.

 

[Bernie Deitrick]

Rich,

There are more than a few ways to do this: here's one, using formulas.

In cell A2:A8, enter 1 in each cell. Then in cell A9, enter

=A2+1

and copy down until you get a complete set of numbers corresponding to the
rows of your original data (say you have 10 rows (rows 3 to 12) so copy down
for 72 rows....)

Then, in B1:??1, enter the numbers 1 through however many columns you want
the old values to be wrapped into - let's say 8 columns, so enter 1 through
8 in B1:I1.

Then in cell B2, enter the formula:

=INDEX(Sheet1!$3:$100,$A2,(COUNTIF($A$2:$A2,$A2)-1)*MAX($B$1:$F$1)+B$1)

and copy over and down to match your new table.

Increase or decrease the 100 of Sheet1!$3:$100 as needed, and change the
sheetname as well.

Personally, I would then copy the entire table and paste values, and finish
by deleting column A and row 1.

HTH,
Bernie
MS Excel MVP