Hi,

I have two workbooks (Book 3 and Report 3) where Report 4 is heavily
linked to Book 3. In Report 4, I have a formula that is referenced to
some columns of Book 3. Now my objective to copy the formula to the
adjecent cell in Report 4 by skipping 5 columns of cells in Book 3.

For Example, in cell c6 of Report 3, I have: -

=SUMIF('[Book3.xls]Line Returns (Internal)'!$E$4:$E$300,
CONCATENATE(LEFT(C$2, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$B
$4:$B$300)

and there is a blank column of cells and in the next column of cells
(cell e6), I want to have this formula: -

=SUMIF('[Book3.xls]Line Returns (Internal)'!$J$4:$J$300,
CONCATENATE(LEFT(C$2, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$G
$4:$G$300)

You can notice the sequence of formulas in report 4, E, J, O etc....
B, G, L etc... I have many columns of cells and it would be hectic to
go to each and every column cell and change the sequence. Is there an
easier way to do this?

I was advised to use OFFSET function but I don't know how to use it
properly and when I tried it, it wasn't skipping any columns in book 3
and copying the formula.

I would appreciate if anyone has a better solution to this. Thank you

Swamy

With Book3.xls open (this is required)

Try this in C6 "of Report 3":

=IF(MOD(COLUMN(A1)-1,2)=1,"",SUMIF(OFFSET('[Book3.xls]Line Returns
(Internal)'!$E$4:$E$300,,INT((COLUMN(A1)-1)/2)*5),LEFT($C2,2)&$A6,OFFSET('[Book3.xls]Line Returns (Internal)'!$B$4:$B$300,,INT((COLUMN(A1)-1)/2)*5)))

Copy C6 across as far as required. It should return exactly what you want.
C6 returns the results from your 1st formula, D6 will return "blank", E6 will
return the results from your 2nd formula, and so on.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
Hi,

I have two workbooks (Book 3 and Report 3) where Report 4 is heavily
linked to Book 3. In Report 4, I have a formula that is referenced to
some columns of Book 3. Now my objective to copy the formula to the
adjecent cell in Report 4 by skipping 5 columns of cells in Book 3.

For Example, in cell c6 of Report 3, I have: -

=SUMIF('[Book3.xls]Line Returns (Internal)'!$E$4:$E$300,
CONCATENATE(LEFT(C$2, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$B
$4:$B$300)

and there is a blank column of cells and in the next column of cells
(cell e6), I want to have this formula: -

=SUMIF('[Book3.xls]Line Returns (Internal)'!$J$4:$J$300,
CONCATENATE(LEFT(C$2, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$G
$4:$G$300)

You can notice the sequence of formulas in report 4, E, J, O etc....
B, G, L etc... I have many columns of cells and it would be hectic to
go to each and every column cell and change the sequence. Is there an
easier way to do this?

I was advised to use OFFSET function but I don't know how to use it
properly and when I tried it, it wasn't skipping any columns in book 3
and copying the formula.

I would appreciate if anyone has a better solution to this. Thank you

Swamy

Hi,

I tried your formula and it doesnt seem to work. The columns dont
change in the formula and it remains constant. What I expect is that
columns in the formula should change to E, J, O...in cells C, E, G in
report 4. I can send you my file so that you can solve the problem. I
would like to know your e-mail address. Thanks for your help.

Swamy



On Mar 13, 1:58 am, Max wrote:
With Book3.xls open (this is required)

Try this in C6 "of Report 3":

=IF(MOD(COLUMN(A1)-1,2)=1,"",SUMIF(OFFSET('[Book3.xls]Line Returns
(Internal)'!$E$4:$E$300,,INT((COLUMN(A1)-1)/2)*5),LEFT($C2,2)&$A6,OFFSET('[*Book3.xls]Line Returns (Internal)'!$B$4:$B$300,,INT((COLUMN(A1)-1)/2)*5)))

Copy C6 across as far as required. It should return exactly what you want.
C6 returns the results from your 1st formula, D6 will return "blank", E6 will
return the results from your 2nd formula, and so on.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---



" wrote:
Hi,

I have two workbooks (Book 3 and Report 3) where Report 4 is heavily
linked to Book 3. In Report 4, I have a formula that is referenced to
some columns of Book 3. Now my objective to copy the formula to the
adjecent cell in Report 4 by skipping 5 columns of cells in Book 3.

For Example, in cell c6 of Report 3, I have: -

=SUMIF('[Book3.xls]Line Returns (Internal)'!$E$4:$E$300,
CONCATENATE(LEFT(C$2, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$B
$4:$B$300)

and there is a blank column of cells and in the next column of cells
(cell e6), I want to have this formula: -

=SUMIF('[Book3.xls]Line Returns (Internal)'!$J$4:$J$300,
CONCATENATE(LEFT(C$2, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$G
$4:$G$300)

You can notice the sequence of formulas in report 4, E, J, O etc....
B, G, L etc... I have many columns of cells and it would be hectic to
go to each and every column cell and change the sequence. Is there an
easier way to do this?

I was advised to use OFFSET function but I don't know how to use it
properly and when I tried it, it wasn't skipping any columns in book 3
and copying the formula.

I would appreciate if anyone has a better solution to this. Thank you

Swamy- Hide quoted text -

- Show quoted text -

The columns don't change in the formula and it remains constant.

Yes, it may appear so, but the OFFSET works that way. The 5 col
skip-incrementing that you want is done by the INT(COLUMN(A1)...)*5 parts
within the OFFSET (these will change the col param) while the front
IF(MOD(COLUMN(A1) ..),"",.. part takes care of "skipping" the cells in
between as you copy the formula across. The incrementer COLUMN(A1) will
return 1 in the starting cell's formula, ie in C6, and it will then provide
the incremental returns: 2,3,4,... as you copy C6 across [COLUMN(B1)=2,
COLUMN(C1)=3, etc]. Try it again. Make sure that Book3.xls is open
simultaneously (as stated earlier, this is a requirement), and also ensure
that calc mode set to auto. I tested it and it works fine here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
oups.com...
Hi,

I tried your formula and it doesnt seem to work. The columns dont
change in the formula and it remains constant. What I expect is that
columns in the formula should change to E, J, O...in cells C, E, G in
report 4. I can send you my file so that you can solve the problem. I
would like to know your e-mail address. Thanks for your help.

Swamy

Hi,

I tried your formula but it doesnt work. The columns in the formula
doesnt change when I copy to the adjacent cells in report 4. I can
send you my excel file to you so that you can make changes and send it
back to me. I would like to know your email address. Thanks for your
help

Swamy


On Mar 13, 1:58 am, Max wrote:
With Book3.xls open (this is required)

Try this in C6 "of Report 3":

=IF(MOD(COLUMN(A1)-1,2)=1,"",SUMIF(OFFSET('[Book3.xls]Line Returns
(Internal)'!$E$4:$E$300,,INT((COLUMN(A1)-1)/2)*5),LEFT($C2,2)&$A6,OFFSET('[*Book3.xls]Line Returns (Internal)'!$B$4:$B$300,,INT((COLUMN(A1)-1)/2)*5)))

Copy C6 across as far as required. It should return exactly what you want.
C6 returns the results from your 1st formula, D6 will return "blank", E6 will
return the results from your 2nd formula, and so on.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---



" wrote:
Hi,

I have two workbooks (Book 3 and Report 3) where Report 4 is heavily
linked to Book 3. In Report 4, I have a formula that is referenced to
some columns of Book 3. Now my objective to copy the formula to the
adjecent cell in Report 4 by skipping 5 columns of cells in Book 3.

For Example, in cell c6 of Report 3, I have: -

=SUMIF('[Book3.xls]Line Returns (Internal)'!$E$4:$E$300,
CONCATENATE(LEFT(C$2, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$B
$4:$B$300)

and there is a blank column of cells and in the next column of cells
(cell e6), I want to have this formula: -

=SUMIF('[Book3.xls]Line Returns (Internal)'!$J$4:$J$300,
CONCATENATE(LEFT(C$2, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$G
$4:$G$300)

You can notice the sequence of formulas in report 4, E, J, O etc....
B, G, L etc... I have many columns of cells and it would be hectic to
go to each and every column cell and change the sequence. Is there an
easier way to do this?

I was advised to use OFFSET function but I don't know how to use it
properly and when I tried it, it wasn't skipping any columns in book 3
and copying the formula.

I would appreciate if anyone has a better solution to this. Thank you

Swamy- Hide quoted text -

- Show quoted text -

Pl see my response to your earlier reply where I've explained things a bit
more. Try it again over there. It should work, with Book3.xls open & calcs
on auto mode. Do a direct copy of the formula from the post, paste it into
the formula bar for C6 (don't retype the long formula, you may introduce
typos). You might need to edit the pasted formula a little due to the
unavoidable line wraps/breaks when you copy from the post, but by and large,
the correction of these line breaks should be fairly obvious and
straightforward.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
oups.com...
Hi,

I tried your formula but it doesnt work. The columns in the formula
doesnt change when I copy to the adjacent cells in report 4. I can
send you my excel file to you so that you can make changes and send it
back to me. I would like to know your email address. Thanks for your
help

Swamy

Hi,

I'll try your solution and see if it works. My message is getting
posted very slowly and it annoys me. I dont know why.

Thanks for your help


On Mar 13, 10:21 am, wrote:
Hi,

I tried your formula but it doesnt work. The columns in the formula
doesnt change when I copy to the adjacent cells in report 4. I can
send you my excel file to you so that you can make changes and send it
back to me. I would like to know your email address. Thanks for your
help

Swamy

On Mar 13, 1:58 am, Max wrote:



With Book3.xls open (this is required)

Try this in C6 "of Report 3":

=IF(MOD(COLUMN(A1)-1,2)=1,"",SUMIF(OFFSET('[Book3.xls]Line Returns
(Internal)'!$E$4:$E$300,,INT((COLUMN(A1)-1)/2)*5),LEFT($C2,2)&$A6,OFFSET('[**Book3.xls]Line Returns (Internal)'!$B$4:$B$300,,INT((COLUMN(A1)-1)/2)*5)))

Copy C6 across as far as required. It should return exactly what you want.