Copying Formula down incrementing every 4 rows

I'm going to simplify my actual situation so its easier to explain:

I have two sheets. Sheet 1 has a bunch of words in column A:
A
apple
banana
carrot
donut

in Sheet 2, i need to create 4 rows of data for EACH row in Sheet 1. it should look like this in sheet 2:
A
"apple +5"
"apple +10"
"apple +15"
"apple +20"
"banana +5"
"banana +10"
"banana +15"
"banana +20"
"carrot +5"
"carrot +10"
"carrot +15"
"carrot +20"

but when i enter my formulas in Sheet 2 and copy down, instead of entering "donut +5" like it should, its grabbing cell A13 from Sheet 1 and skipping everything entered from A4-A12. how can i get it so the formula grabs A1 for the first 4 rows, A2 for the next 4, A3 for the next 4 and so on. Thanks.

Originally Posted by XOR LX

Hi,

=IF(ROWS($1:1)>4*COUNTA(Sheet1!$A$1:$A$4),"",INDEX(Sheet1!$A$1:$A$4,1+INT((ROWS($1:1)-1)/4))&" +"&5+MOD(5*(ROWS($1:1)-1),5*COUNTA(Sheet1!$A$1:$A$4)))

Copy down until you start to get blanks.

Regards

Thanks, it appears thats what I need. Only problem is my formula is a little more complicated than just adding +5 +10 +15 to the end.

Could you perhaps modify it so it will just output the value from Sheet 1 four times? So it looks something like this in Sheet 2:

apple
apple
apple
apple
banana
banana
banana
banana
carrot
carrot
carrot
carrot

then i can work with adding my formula to those. thanks for the help

Originally Posted by XOR LX

Just remove everything after and including &" +"...

Regards

When I do that and copy it down im just getting "apple" in every row. the formula i entered is:
=IF(ROWS(1:1)>4*COUNTA(Sheet1!$A$1:$A$4),"",INDEX(Sheet1!$A$1:$A$4,1+INT((ROWS(1:1)-1)/4)))

had to add an extra bracket on the end after i deleted everything after &" +"

Originally Posted by Tony Valko

Here's another one.

Data Range

	A	B
1	apple	apple
2	banana	apple
3	carrot	apple
4	donut	apple
5		banana
6		banana
7		banana
8		banana
9		carrot
10		carrot
11		carrot
12		carrot
13		donut
14		donut
15		donut
16		donut
17

This formula entered in B1:

=IFERROR(INDEX(A$1:A$4,CEILING(ROWS(B$1:B1)/4,1)),"")

Copy down until you get blanks.

thanks this works. thanks to XOR LX for the help as well. cheers