Excel formula referencing cells

  • Thread starter Thread starter Mats Lingblad
  • Start date Start date
M

Mats Lingblad

I have a normal list in Sheet1

In Sheet2 I would like to use the information in Sheet1 to print
flashcards (used for learning languages). I have each flashcard in
Sheet two consisting of 10x10 cells. I have formatted these flashcards
so that I get ten for each printed page. I would like to simply
transfer information from Sheet1 to Sheet2

Normally when you cut and paste formulas they are kind of intelligent.
If I copy and paste =Sheet1!A1 it will change to =Sheet1!A2, =Sheet1!
A3, ..., for each subsequent line. This does not work in my case.

In Sheet2 I would like to enter a field on every tenth line, but
referencing each consequtive line in Sheet1

Sheet2 cell Formula
A2 =Sheet1!A2
A12 =Sheet1!A3
A22 =Sheet1!A4
.... ...

Is this possible?


Rds, matsl
 
Put this in A2 of Sheet2:

=INDEX(Sheet1!A:A,INT((ROW(A2)+8)/10)+1)

Then you can copy it to A12, A22, A32 etc.

Hope this helps.

Pete
 
Thanks Pete. That was a very cleaver solution.

One additional complication, I have two flashcards next to each other
in Sheet2. This means that I would like to reference Sheet1 in the
following manner

A2 A3
A4 A5
A6 A7

The formula you gave me does the following

A2
A3
A4

Naturally that is very functional but I will waste a lot of paper. I
have played around with your formula, but I can't get my brain around
it. So I'm seeking your help again.

Mas
 
Hi Mats,

I'm just about to go out and I saw your post.

I'll get back to you a bit later on.

Pete
 
Hello again, Mats.

You need to put this formula in A2 of Sheet2:

=INDEX(Sheet1!A:A,INT((ROW(A2)+8)/10)*2)

and when you copy this to A12, A22, A32 etc it will refer to A4, A6,
A8, and so on.

Similarly, in your other cell on row 2 you need this formula:

=INDEX(Sheet1!A:A,INT((ROW(A2)+8)/10)*2+1)

and that will give you the sequence A3, A5, A7 etc when copied to rows
12, 22, 32...

Hope this helps.

Pete
 
Back
Top