Moving Range In Large Formula

[Lewzerrrr]

Hi

I have some data in sheet2 that has sales data by week, it consists of 5 columns per week.

In sheet1, I need to pull the largest number's by week so my starting formula would be =LARGE(Sheet2!I$11:I$30003,1)

I then need to drag it to the next cell which is next week, the new formula has to be =LARGE(Sheet2!N$11:N$30003,1) but obviously this won't happen. Do I need to use ADDRESS or OFFSET?

Thanks

 

[StephenCrump]

If your first formula is in A1, and you are copying to the right:

=LARGE(INDEX(Sheet2!$I$11:$Z$30003,,5*COLUMNS($A1:A1)-4),1)

If you are copying down, change COLUMNS($A1:A1) to ROWS(A$1:A1)

Adjust the column Z reference depending on the width of the data.