Continue a vertical pattern on an adjacent column

Hello,

I am trying to make printable book labels in one worksheet based off of values from another. The labels that I have are standard address sized and come in sheets with 3 columns and 10 rows. My "label" worksheet has 5 columns (with B:B and D:D as "spacers") I made a "calculation" worksheet that simply runs the equation I want to use for the labels from A1:A1000. On the "label" worksheet, I have this formula in cell A1:

=IF('Label Calculations'!$A1="","",'Label Calculations'!$A1)

This way, it won't return 0s for blank cells from the "calculations" worksheet. I dragged down the formula to cell A10, but would like to continue the pattern so that cell C1 would be:

=IF('Label Calculations'!$A11="","",'Label Calculations'!$A11)

and cell E1 would be:

=IF('Label Calculations'!$A21="","",'Label Calculations'!$A21)

and the pattern would continue with cell A11 with the formula:

=IF('Label Calculations'!$A31="","",'Label Calculations'!$A31)


Then cell B11 would be 41, C11 would be 51, A21 would be 61, etc.

I can do it manually by adding the function in the top row of each "page" (A1,C1,E1 then A11,C11,E11...) and dragging them down each "page." However, I may end up with 20 some pages of labels. I feel like there should be an easier way I can do this. Any thoughts?

Thanks in advance!

Put this in cell A1:

=IF(INDEX('Label Calculations'!$A:$A,MOD(ROWS($1:1)-1,10)+1+INT((ROWS($1:1)-1)/10)*30)="","",INDEX('Label Calculations'!$A:$A,MOD(ROWS($1:1)-1,10)+1+INT((ROWS($1:1)-1)/10)*30))

Copy the formula into C1 and into E1, then edit the formula slightly so that C1 contains:

=IF(INDEX('Label Calculations'!$A:$A,MOD(ROWS($1:1)-1,10)+11+INT((ROWS($1:1)-1)/10)*30)="","",INDEX('Label Calculations'!$A:$A,MOD(ROWS($1:1)-1,10)+11+INT((ROWS($1:1)-1)/10)*30))

and E1 contains:

=IF(INDEX('Label Calculations'!$A:$A,MOD(ROWS($1:1)-1,10)+21+INT((ROWS($1:1)-1)/10)*30)="","",INDEX('Label Calculations'!$A:$A,MOD(ROWS($1:1)-1,10)+21+INT((ROWS($1:1)-1)/10)*30))

Then you can copy those formulae down as far as you need to.

Hope this helps.

Pete

Pete,

That worked PERFECTLY! Thank you SO much! I guess I need to do some more research on the INDEX function

Originally Posted by gkostrom

That worked PERFECTLY! Thank you SO much! ...

You're welcome - thanks for feeding back.

Originally Posted by gkostrom

... I guess I need to do some more research on the INDEX function

Actually, it's the MOD and INT functions that do all the work, generating the appropriate numbers to access cells in column A via the INDEX function, so perhaps you should look into those.

Hope this helps.

Pete