Inconsistent formula when referencing same cell n times

[dj1274]

Hi all, I hope someone can help me with one that's driving me mad ...

I have a workbook with two sheets - "Sheet 1" contains master data and "Sheet 2" in building modified data from "Sheet 1"

Sheet 1
[TABLE="width: 112"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762[/TD]
[/TR]
[TR]
[TD="align: right"]3188642258182[/TD]
[/TR]
[TR]
[TD="align: right"]3188642258199[/TD]
[/TR]
</tbody>[/TABLE]

I need "Sheet 2" to look like this after referencing A2 then A3 etc

Sheet 2
[TABLE="width: 99"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642010025[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642037114[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762[/TD]
[/TR]
[TR]
[TD="align: right"]3188642146762

[/TD]
[/TR]
</tbody>[/TABLE]
I have tried ='Sheet 1'!A2 in cells Sheet 2 A2:A11 then ='Sheet 1'!A3 in Sheet 2 A12:A21

I then select all 20 cells and drag down but the cell reference does not follow the pattern and starts to rise in increments

Basically, I am trying to reference each cell from A2 in Sheet 1 ten times in sheets 2

Any help on this would be most welcome.

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Try this formula in Sheet2 A2

=INDEX(Sheet1!$A$2:$A$6,INT((ROWS(A$2:A2)-1)/10)+1)

copy down

Format as Number 0 decimal places

M.