When dragging a formula down a worksheet move the cell references down 5 cells each time

[lukela85]

Hi all,

I'm creating a spreadsheet which will list the daily closing prices for all major foreign exchange currency pairings for the last 5 years. I would like to be able to work out the percentage change for each 13 week period (5 day financial week = 65 days) on a rolling basis. So the first formula is currently =(AUDJPY!B67/AUDJPY!B2)-1. The formula on the next row would be (AUDJPY!B72/AUDJPY!B7)-1. When the formula is dragged down the sheet is there a way to get Excel to increase the cell references in the formula by 5 instead of the usual one?

I have attached a very basic worksheet.

Thanks,

Luke

[BadlySpelledBuoy]

In B2 and copied down:

Please Login or Register  to view this content.

I'm sure there's an easier way but it's hot, I'm tired and I'm out of beer so my brain isn't working well!

BSB

[lukela85]

Thanks, works perfectly!