I need to move a formula 10 columns to the right

[kyddrivers]

I am pulling multiple columns into a new sheet. The first reference is in cell F16 (Sun!F16), and the next one is 10 columns to the right or P16 (Sun!P16). On the new sheet, I would like to write a formula in I2 that offsets the formula in B2 by 10 instead of having to manually do it and introduce human error across 42 rows of data. I hope that makes sense.

I have tried to do offset(indirect and I just keep getting errors. The

Thanks in advance!

Book1
	B	C	D	E	F	G	H	I
1	3/26	3/27	3/28	3/29	3/30	3/31	4/1	4/2
2	12	0	0	0	0	0	0	12
Monthly req

Cell Formulas
Range	Formula
B2	B2	='Sun'!F16
C2	C2	='Mon'!F16
D2	D2	='Tue'!F16
E2	E2	='Wed'!F16
F2	F2	='Thu'!F16
G2	G2	='Fri'!F16
H2	H2	='Sat'!F16
I2	I2	='Sun'!P16

 

[offthelip]

try this:

=OFFSET(Sun!$F16,0,10*INT(COLUMN()/8))

 

[kyddrivers]

A couple of questions:
1) Can you explain how this formula works? I understand the basic premise of the offset, but not sure why we are using the INT, Column, and the division by 8.
2) Can I drag this formula across 42 columns and 96 rows?

Thanks

 

[offthelip]

Actually as I started to described this I realised I had pasted your data into column A not column B, the correct equation is:

=OFFSET(Sun!$F16,0,10*INT((COLUMN()-1)/8)))

Note the minus 1
So starting with this updated equation :The reason for 10*Int(Column()-1/8 is there are 8 column between B and I so the )column()-1)/8 will give you , 0.125, 0.25 , 0.375, 0.5 ... 0.875 , 1 as it goes from column B to Column I. If you take the integer value of this you get 0 for columns B to H and 1 in column I.
You want to reference column F ( which is column 6) for column B to H and then Column P ( which is column 16) for I onwards. These are 10 column apart, so multiplying the Int((column()-1)/8) by 10 will give us the correct offset.
This equation will work for column B and Column I, but does not work for column C to G because these all reference different worksheets. If you copy this from column B to column C to H and then change the references to the correct worksheets then you can copy that group for 7 columns across as many time as you want and it will update to successive column.
Another alternative is to use VBA to write the correct equations without using offset which will mean you workhseet will calculate much faster[/CODE]

 

[offthelip]

Sorry that last post got a bit screwed up when I pasted the equation. the correct equation is:

=OFFSET(Sun!$F16,0,10*INT((COLUMN()-1)/8))

An alternative which will write the equations directly using vba; is to run this code. give it a try on a copy of your workbook:

Sub test()
 nam = Array("&Sun!", "&Mon!", "&Tue!", "&Wed!", "&Thu!", "&Fri!", "&Sat!")
ID = 0
Colno = 6
For i = 2 To 42
collet = Split(Cells(1, Colno).Address, "$")(1)
Range(Cells(8, i), Cells(8, i)) = nam(ID) & collet & "16"
ID = ID + 1
 If ID > 6 Then
 ID = 0
 Colno = Colno + 10
 End If
 Next i
 

End Sub

change the ampersands to equals signs to get it to make the correct references, I used ampersands just for testing it.