| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
fill down formula incrementing by every 12 rows
Hi - I want to drag/fill down a formula in 12 rows (eg A1 to A12)as follows
=Sheet1A2 =Sheet1A2 =Sheet1A2 =Sheet1A2 =Sheet1A3 =Sheet1A3 =Sheet1A3 =Sheet1A3 I want it to increment by 1. eg next 12 rows would =Sheet1A4 etc Selecting the entire range does not work, it just keeps returning the same value eg Sheet1A2 Thanks Craig |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
fill down formula incrementing by every 12 rows
So, you want to repeat the reference 4 times then increment by 1?
Try this: =INDEX(Sheet1!A$2:A$65536,CEILING(ROWS($1:1)/4,1)) -- Biff Microsoft Excel MVP "westy" wrote in message ... Hi - I want to drag/fill down a formula in 12 rows (eg A1 to A12)as follows =Sheet1A2 =Sheet1A2 =Sheet1A2 =Sheet1A2 =Sheet1A3 =Sheet1A3 =Sheet1A3 =Sheet1A3 I want it to increment by 1. eg next 12 rows would =Sheet1A4 etc Selecting the entire range does not work, it just keeps returning the same value eg Sheet1A2 Thanks Craig |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
fill down formula incrementing by every 12 rows
Another way
In say, Sheet2's B2, copied down as far as required: =OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/4),) will "repeat the reference 4 times then increment by 1" The starting reference is: =Sheet1A2 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "westy" wrote: Hi - I want to drag/fill down a formula in 12 rows (eg A1 to A12)as follows =Sheet1A2 =Sheet1A2 =Sheet1A2 =Sheet1A2 =Sheet1A3 =Sheet1A3 =Sheet1A3 =Sheet1A3 I want it to increment by 1. eg next 12 rows would =Sheet1A4 etc Selecting the entire range does not work, it just keeps returning the same value eg Sheet1A2 Thanks Craig |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Incrementing a formula by X number | Excel Worksheet Functions | |||
| formula will not fill correctly due to odd number rows | Excel Worksheet Functions | |||
| Incrementing formula by two rows | Excel Worksheet Functions | |||
| Automatically 'incrementing' formulas for new rows. | New Users to Excel | |||
| Incrementing rows (or columns) to a reference in another worksheet | Excel Worksheet Functions | |||
Hybrid Mode
