looking for a quick way to fill cells (with a twist)

[Dominique Schroeder]

Hi,

I have to populate an Excel spreadsheet which acts as a calendar for the
current year.

The problem I have is that the first worksheet has a row of contiguous
cells that reference values in cells of another spreadsheet(called in this
case DCS manpower) which are distanced every 6 columns away.

In other words, cell 1 references: 'DCS manpower'!$A1
cell 2 references: 'DCS manpower'!$F1
cell 3 references: 'DCS manpower'!$K1
cell 4 references:'DCS manpower'!$P1 etc.

Is there any way that I can right-fill that row so I don't have to manually
point to cells?

thanks,

Dominique

 

[Max]

One way:

Put in A1: =OFFSET('DCS manpower'!$A$1,ROW()-1,COLUMN()*5-5)

Copy A1 right

(You can copy the formula down as well to get corresponding pickups)

 

[Max]

Just to add on a little ..

Remove the "ROW()-1" part if you just want to grab / copy across

Put in any cell in col A, say in A5

: =OFFSET('DCS manpower'!$A$1,,COLUMN()*5-5)

Copy A5 across