Continuation of an Offset

**Ashley Excel** · 01-18-2009, 01:06 PM

This seems like it should be so easy . . . but I can't figure it out.

I need to have a worksheet (Main Worksheet) that combines information from several different worksheet (Sub Worksheet 1, Subworksheet 2, Subworksheet 3, etc.) in the same workbook. The Sub worksheets are set up exactly the same . . . Now, in the Main Worksheet, I need in Cell C7 the information from Sub Worksheet 1 Cell E10 THEN in Cell D7 of Main Worksheet I need to offset Sub Worksheet 1 Cell E10 7 (seven) columns. I need to continue the offset in Main Worksheet 7 columns.

In other words, I need to find a way to automatically tell Excel to do the following:

Main Worksheet C7 = Sub Worksheet 1 Cell E10
Main Worksheet D7 = Sub Worksheet 1 Cell E17
Main Worksheet E7 = Sub Worksheet 1 Cell E24
Main Worksheet F7 = Sub Worksheet 1 Cell E31

Also, since I won't be updating this weekly, and the information is coming from an in-use database, this system needs to be (if possible) usable by novice Excel users.

Help! Help! Help!

**DonkeyOte** · 01-18-2009, 01:13 PM

One way...

Main Worksheet

C7: =INDEX('Sub Worksheet 1'!$E$10:$E31,1+(7*(COLUMNS($A7:C7)-COLUMN($C7))),1)

copy across to F7.

**Ashley Excel** · 01-18-2009, 01:36 PM

Thank you very Very VERY Much, DonkeyOte!

Could I ask just one more favor: Could you please explain the formula, convert it to math, so I can understand and use it? I have a vague understanding of the formula but I shot myself in foot by using C7 and asking for a 7 offset.

**DonkeyOte** · 01-18-2009, 02:41 PM

It's not the most elegant but

INDEX(range,row,column)

with the following

INDEX(A1:C10,4,2)

would return contents of B4 (row 4, column 2 of the range A1:C10)

In your example Sheet!E10:E31 the column is constant given you only reference one column in your range so I set to 1 (you could omit), the variable in your INDEX is the row value... for C7 you want to return E10 (ie row 1 of the range), for D7 you want to return E17 (row 8 of the range), so the

1+(7*(COLUMNS($Ax:Yx)-COLUMN($Cx)))
(where x is row of cell in which formula resides and Y is meant to represent the column in which the formula resides)

the above will take 1 and then add 7 * number of columns past C ... so if in C you will get 1 + (7*0) -> row 1 (E10), for D you will get 1 + (7*1) -> row 8 (E17), for E: 1 + (7*2) -> row 15 (E24) etc...

You could dispose of the COLUMN($Cx) and replace with the value 3 -- it is used merely to represent the column number of the cell that is returning the contents of E10.