Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Incrementing Cell Reference Values by 7?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Incrementing Cell Reference Values by 7?
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 "Cheri Firlit" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Incrementing Cell Reference Values by 7?
Thank you so much!!! "Shane Devenshire" wrote: 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 "Cheri Firlit" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rang reference incrementing by more than one on formula drag | Excel Worksheet Functions | |||
Incrementing cell reference | Excel Discussion (Misc queries) | |||
Incrementing cell reference in Lookup formula | Excel Worksheet Functions | |||
Dragging & incrementing cell values | Excel Worksheet Functions | |||
incrementing formula reference by 7 | Excel Discussion (Misc queries) |