Sequentially copy formula when dragging over a range

[blutak]

Hi All,

First time on this forum and hoping you can help! I have a very simple 'formula' that just refers to a cell on another sheet e.g. (A1 = 'Q1 2012'!A5). Problem is, cell A1 is a merged set of cells (A1:A20) and when I drag the formula down the sheet the next cell references cell A26 from the other sheet when I need it to reference A2. Is there amy way to get it to reference the next cell each time i drag it down? I have 750 rows to do this for and don't fancy entering it every single time.

Many thanks for your help in advance. If you need more information from me please let me know.

[Don]

Hi and welcome to the forum!

Here is a formula that should do the trick.

Put this in cell A1:

Code: Select all

='Q1 2012'!A5

Put this in the cells below that (A21, A41, etc.) by copying them down:

Code: Select all

=INDIRECT("'Q1 2012'!A" & TRUNC((ROW()/20)+5))

There may be better ways to do this, but this should do the trick for you.

[blutak]

Don, you are a gentleman and a scholar! Thank you, that does exactly what I need it to, even after my clear-as-mud explanation!