Copy & Paste Fill Change

[dstryker24]

When doing a normal copy and paste of a formula you get 1 cell added to the formula

ie... a1=b1+c1
when dragged and copied to a2 you get a2=b2+c2

Is there a way to make it add more than one cell so that when the first formula is dragged and copied I would get a2=b17+c17

The actual formula I am doing a copy and paste with is:

=IFERROR(CHOOSE($I$2,'YTD & MTD'!D58,'YTD & MTD'!E58,'YTD & MTD'!F58,'YTD & MTD'!G58,'YTD & MTD'!H58,'YTD & MTD'!I58,'YTD & MTD'!J58,'YTD & MTD'!K58,'YTD & MTD'!L58,'YTD & MTD'!M58,'YTD & MTD'!N58,'YTD & MTD'!O58,'YTD & MTD'!C58),"")

I am needing to copy and paste to the next row but want 'YTD & MTD'D58 to change to 'YTD & MTD'74

Thanks in advance!

[benishiryo]

hi dstryker24, here are 2 ways:
INDIRECT("'YTD & MTD'!D"&58+(ROW(1:1)-1)*16)
or:
INDEX('YTD & MTD'!D:D,58+(ROWS(1:1)-1)*16)

this is for value1. use the same formula but change the column number for the rest.