Offset? Vlookup? Cell referencing

[dmurphy1991]

So my problem is, i have a row of data, but due to cell merging and it's layout the cells i want are spaced 3 columns apart.

I would like to be able to use the above cell's formula, but then add 3 onto the column number for the next cell.

i.e. A1 formula = 'Worksheet1'!D2
I'd want A2 formula = D2 (but with the column reference +3)
- 'Worksheet1'!(D+3)2
This would give me G2 as a cell reference for A2, however i have a very large list and would like to not have to manually change the reference for all individually

and i'd like this to be formatted so i could drag it down the list and the process would repeat.

Is there a way of doing this?

 

[venkat1926]

not at all clear

try something like this

=OFFSET(D2,0,3,1,1)

 

[Teeroy]

I think you'll need to use either OFFSET or INDIRECT. Be careful as these are both volatile functions and can slow down recalculation in a large spreadsheet.
You've got an example with OFFSET so for INDIRECT you could use:

=INDIRECT(ADDRESS(2,3*(ROW())+1,1,1,"Worksheet1"))

When you drag this down it will move 3 columns to the right at each row.