How do I Incrementing Cell Reference Values by 7?

[Cheri Firlit]

I want to do 2000 lines of data coming from another worksheet. To get the
first cell of information I want the formula is

cell A2 formula
=IF('[Customer Information.xls]A'!$E$6 ="","",'[Customer
Information.xls]A'!$E$6)

cell A3 formula
=IF('[Customer Information.xls]A'!$E$13 ="","",'[Customer
Information.xls]A'!$E$13)

cell A4 formula
=IF('[Customer Information.xls]A'!$E$20 ="","",'[Customer
Information.xls]A'!$E$20)

I have tried the "fill-in method" where I took out the $ so the formulas read

cell A2 formula
=IF('[Customer Information.xls]A'!$E6 ="","",'[Customer
Information.xls]A'!$E6)

cell A3 formula
=IF('[Customer Information.xls]A'!$E13 ="","",'[Customer
Information.xls]A'!$E13)

cell A4 formula
=IF('[Customer Information.xls]A'!$E20 ="","",'[Customer
Information.xls]A'!$E20)

I hoped the formula for A5 would be

=IF('[Customer Information.xls]A'!$E27 ="","",'[Customer
Information.xls]A'!$E27)

Unfortunately it was:
=IF('[Customer Information.xls]A'!$E9 ="","",'[Customer
Information.xls]A'!$E9)

What am I doing wrong? Is there a way to make this increment the way I want
it to?

Thanks for your help.

 

[Shane Devenshire]

Hi,

Try modifying your reference using

=INDIRECT("E"&6+7*(ROW(A1)-1))

so
=IF('[Customer Information.xls]A'!$E$6 ="","",'[Customer
Information.xls]A'!$E$6)

becomes

=IF(INDIRECT("'[Customer Information.xls]A'!$E$"&6+7*(row(a1)-1))
="","",INDIRECT("'[Customer Information.xls]A'!$E$"&6+7*(row(a1)-1)) )

Watch those double and single quotes.

 

[Cheri Firlit]

Thank you so much!!!

Hi,

Try modifying your reference using

=INDIRECT("E"&6+7*(ROW(A1)-1))

so
=IF('[Customer Information.xls]A'!$E$6 ="","",'[Customer
Information.xls]A'!$E$6)

becomes

=IF(INDIRECT("'[Customer Information.xls]A'!$E$"&6+7*(row(a1)-1))
="","",INDIRECT("'[Customer Information.xls]A'!$E$"&6+7*(row(a1)-1)) )

Watch those double and single quotes.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

I want to do 2000 lines of data coming from another worksheet. To get the
first cell of information I want the formula is

cell A2 formula
=IF('[Customer Information.xls]A'!$E$6 ="","",'[Customer
Information.xls]A'!$E$6)

cell A3 formula
=IF('[Customer Information.xls]A'!$E$13 ="","",'[Customer
Information.xls]A'!$E$13)

cell A4 formula
=IF('[Customer Information.xls]A'!$E$20 ="","",'[Customer
Information.xls]A'!$E$20)

I have tried the "fill-in method" where I took out the $ so the formulas read

cell A2 formula
=IF('[Customer Information.xls]A'!$E6 ="","",'[Customer
Information.xls]A'!$E6)

cell A3 formula
=IF('[Customer Information.xls]A'!$E13 ="","",'[Customer
Information.xls]A'!$E13)

cell A4 formula
=IF('[Customer Information.xls]A'!$E20 ="","",'[Customer
Information.xls]A'!$E20)

I hoped the formula for A5 would be

=IF('[Customer Information.xls]A'!$E27 ="","",'[Customer
Information.xls]A'!$E27)

Unfortunately it was:
=IF('[Customer Information.xls]A'!$E9 ="","",'[Customer
Information.xls]A'!$E9)

What am I doing wrong? Is there a way to make this increment the way I want
it to?

Thanks for your help.

 

[Bill Kuunders]

one way


=IF(OFFSET([Customer
Information.xls]A!$E$6,(ROWS($1:1)-ROW($1:1))*7,,)="","",OFFSET([Customer
Information.xls]A!$E$6,(ROWS($1:1)-ROW($1:1))*7,,))

and extend down.................

good luck