How to copy a formula to a new cell and jump the cell reference more than one cell

[sarkman22]

I am hoping there is a simple solution to this- if it's complicated in Visual, it's probably not worth it!

Issue: I have 2 worksheets that I'm setting up to talk to each other. Worksheet 1 has hundreds of entries for students in a "form" like setup. Each entry is setup on one page. Student #1 is A1:G46, Student #2 is H1:N46, etc. This format is set and can not be changed.

Worksheet 2 is acting as a "summary" worksheet, and referring to worksheet 1's information for each student. This format is flexible, but I currently have it setup that each row shows a summary for 1 student, each column is a different piece of info. The summary includes just simple cell reference and vlookup to Worksheet 1. The problem I am having is that I want to copy the formulas from one row to another row in Worksheet 2, but jump the appropriate number of cells, instead of the default of one.

An example is probably best to describe this (see attached):

On my summary page (worksheet 2), Cell B5 simply refers to cell B1 on worksheet 1 for Student 1. When I copy that formula down to cell B6 for Student 2, the formula auto updates to refer to cell B2 on worksheet 1. However, I want it to refer to cell I1 on worksheet 1 (7 rows to the right of B1). And then continue this pattern so I auto copy the formula to B7 in worksheet 2, and it automatically refers to 7 more rows to the right (P1).

Is this possible to automate?

Sorry I tought this would take less effort to explain, let me know if you have questions and thank you in advance!

[Pauleyb]

Welcome to the world of INDEX and OFFSET. Both can be used for something like this, but I'll use OFFSET in this example. In cell B5 of Worksheet2:
=OFFSET('Worksheet 1'!$B$1,0,(ROW()-ROW($A$5))*7)
Drag this formula down.

This basically says, using cell Worksheet1!B1 as a starting point, go down 0 columns, and then go right 7*(Row offset from row 5). Since this formula starts in cell B5, the row offset is 0, so it goes right 0 cells form the B1 starting point, thus giving you the value in B1. When you go to the next row, the offset becomes 1, so now it goes 0 down and 7 right from the starting point, which is I1, and it give you the next name.

Using this technique, you can fill out the rest of your table.

[sarkman22]

This is fabulous....much obliged! I think it will work out perfectly, I'll try it out today.