Adding one to a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a list of data items that are spread across a row, I want to display them over 10 rows and a couple of columns. I am able to do this OK. =sheet1!ab1 etc. After adding the 10 rows, I skip a space and instead of editing the formula, I want to show the next line of data. I tried using sheet1!ab1+1, but it errors. Is there a way to do this?

Thanks in advance.

Doug
 
Hi Doug
if I understood you correctly you want to display 10 columns per row
and your source data is one large row (up to 255 rows). If yes enter
the following in cell A1 on your target cell (assumption sheet1!AB is
your source row)
=OFFSET('sheet1'!$AB$1,0,(COLUMN()-1)+(ROW()-1)*10)
copy this left and down

HTH
Frank
 
I'm not sure I followed what you are trying to do but it sounds like yo
need to use .Offset. This allows you to specify a row and/or colum
offset from a specific cell;

e.g. Range("AB1").Offset(1,0) is equivalent to Range("AB2"
 
I probably confused you all. This is what I need

I have this

=Sheet1!A1
=Sheet1!D1 =Sheet1!E1
=Sheet1!$J1 =Sheet1!$K1 =Sheet1!$AB1 =Sheet1!$AC1
=Sheet1!$L1 =Sheet1!$M1 =Sheet1!$AD1 =Sheet1!$AE1
=Sheet1!$N1 =Sheet1!$O1 =Sheet1!$AF1 =Sheet1!$AG1
=Sheet1!$P1 =Sheet1!$Q1 =Sheet1!$AH1 =Sheet1!$AI1
=Sheet1!$R1 =Sheet1!$S1 =Sheet1!$AJ1 =Sheet1!$AK1
=Sheet1!$T1 =Sheet1!$U1 =Sheet1!$AL1 =Sheet1!$AM1
=Sheet1!$V1 =Sheet1!$W1 =Sheet1!$AN1 =Sheet1!$AO1
=Sheet1!$X1 =Sheet1!$Y1 =Sheet1!$AP1 =Sheet1!$AQ1
=Sheet1!$Z1 =Sheet1!$AA1 =Sheet1!$AR1 =Sheet1!$AS1


You see all these are for row one. This is all one record.
I want to skip a line and then do the same thing but using row two.

Not sure if OFFSET will help me or not.

Thanks,
Doug
 
Hi Doug
now I'm confused :-)
what do you mean with 'one record' or 'skip a line'?
maybe just explain what you want to have in cell A1 on your target
sheet and what is the source for this cell. In your example i'm not
sure what you are trying to say with this line for example:
=Sheet1!$J1 =Sheet1!$K1 =Sheet1!$AB1 =Sheet1!$AC1

Frank
 
Hi Frank,

Sorry about that. What I have there is all one record, it has four columns
and 11 rows.

I want to display the rest of the rows in the same format.

Row #1's data

=Sheet1!A1
=Sheet1!D1 =Sheet1!E1
=Sheet1!$J1 =Sheet1!$K1 =Sheet1!$AB1 =Sheet1!$AC1
=Sheet1!$L1 =Sheet1!$M1 =Sheet1!$AD1 =Sheet1!$AE1
=Sheet1!$N1 =Sheet1!$O1 =Sheet1!$AF1 =Sheet1!$AG1
=Sheet1!$P1 =Sheet1!$Q1 =Sheet1!$AH1 =Sheet1!$AI1
=Sheet1!$R1 =Sheet1!$S1 =Sheet1!$AJ1 =Sheet1!$AK1
=Sheet1!$T1 =Sheet1!$U1 =Sheet1!$AL1 =Sheet1!$AM1
=Sheet1!$V1 =Sheet1!$W1 =Sheet1!$AN1 =Sheet1!$AO1
=Sheet1!$X1 =Sheet1!$Y1 =Sheet1!$AP1 =Sheet1!$AQ1
=Sheet1!$Z1 =Sheet1!$AA1 =Sheet1!$AR1 =Sheet1!$AS1

Row #2's data (THIS IS WHAT I WOULD LIKE TO SEE)
*** Is there an easy way to do this ? ****

=Sheet1!A2
=Sheet1!D2 =Sheet1!E2
=Sheet1!$J2 =Sheet1!$K2 =Sheet1!$AB2 =Sheet1!$AC2
=Sheet1!$L2 =Sheet1!$M2 =Sheet1!$AD2 =Sheet1!$AE2
=Sheet1!$N2 =Sheet1!$O2 =Sheet1!$AF2 =Sheet1!$AG2
=Sheet1!$P2 =Sheet1!$Q2 =Sheet1!$AH2 =Sheet1!$AI2
=Sheet1!$R2 =Sheet1!$S2 =Sheet1!$AJ2 =Sheet1!$AK2
=Sheet1!$T2 =Sheet1!$U2 =Sheet1!$AL2 =Sheet1!$AM2
=Sheet1!$V2 =Sheet1!$W2 =Sheet1!$AN2 =Sheet1!$AO2
=Sheet1!$X2 =Sheet1!$Y2 =Sheet1!$AP2 =Sheet1!$AQ2
=Sheet1!$Z2 =Sheet1!$AA2 =Sheet1!$AR2 =Sheet1!$AS2

Row #3's data , etc until the EOF

=Sheet1!A3
=Sheet1!D3 =Sheet1!E3
=Sheet1!$J3 =Sheet1!$K3 =Sheet1!$AB3 =Sheet1!$AC3
=Sheet1!$L3 =Sheet1!$M3 =Sheet1!$AD3 =Sheet1!$AE3
=Sheet1!$N3 =Sheet1!$O3 =Sheet1!$AF3 =Sheet1!$AG3
=Sheet1!$P3 =Sheet1!$Q3 =Sheet1!$AH3 =Sheet1!$AI3
=Sheet1!$R3 =Sheet1!$S3 =Sheet1!$AJ3 =Sheet1!$AK3
=Sheet1!$T3 =Sheet1!$U3 =Sheet1!$AL3 =Sheet1!$AM3
=Sheet1!$V3 =Sheet1!$W3 =Sheet1!$AN3 =Sheet1!$AO3
=Sheet1!$X3 =Sheet1!$Y3 =Sheet1!$AP3 =Sheet1!$AQ3
=Sheet1!$Z3 =Sheet1!$AA3 =Sheet1!$AR3 =Sheet1!$AS3
 
Hi
Not sure I understood you but why don't you just copy the formula to
the next row. Excel will change the row index from 1 to 2
automatically. So no need to do anything.

But I'm quite sure I'm missing something here :-). If you like, you can
send me an example of your spreadsheet and I'll look into it
Frank
 
I would like to have each record (row) displayed in a report format that
will have 11 rows, so copying the formula won't work.

I just sent you a quick example. The example I sent I want the data
displayed in three rows.

Doug
 
Back
Top