Skip columns and copy formulas

[mahadevan.swamy]

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

 

[Guest]

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.

 

[mahadevan.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

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
---

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 -

 

[Max]

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.

---
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

 

[mahadevan.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

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
---

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 -

 

[mahadevan.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

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

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
---

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 -- Hide quoted text -

- Show quoted text -

 

[Max]

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.

---
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

 

[mahadevan.swamy]

Hi Max,

Thanks for your solution. It worked.

I have another problem in another report (report 3). The formulas from
cell c6 to s6 remains the same where it doesnt skip any columns in
book 3. But from U6 to AK6 , it should skip 5 columns in book 3.

For example, I have in cell c6: -
=SUMIF('[Book3.xls]Line Returns (Internal)'!$E$4:$E$300,
CONCATENATE(LEFT(C$3, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$B
$4:$B$300)

in cell e6,

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

in cell u6,

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

I know that I have to use the same approach but since I am running out
of time, I would appreciate if you can please help me with that
formula. I'll study your formula later and ask if I have any
questions. Thanks

Mahadevan 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

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

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
---
:
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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[mahadevan.swamy]

But from U6 to AK6 , it should skip 5 columns in book 3

Please allow me to make the problem more clear. From cells U6 to AK6,
the formula must remain constant but should have skipped 5 columns in
book 3.

Hi Max,

Thanks for your solution. It worked.

I have another problem in another report (report 3). The formulas from
cell c6 to s6 remains the same where it doesnt skip any columns in
book 3. But from U6 to AK6 , it should skip 5 columns in book 3.

For example, I have in cell c6: -
=SUMIF('[Book3.xls]Line Returns (Internal)'!$E$4:$E$300,
CONCATENATE(LEFT(C$3, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$B
$4:$B$300)

in cell e6,

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

in cell u6,

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

I know that I have to use the same approach but since I am running out
of time, I would appreciate if you can please help me with that
formula. I'll study your formula later and ask if I have any
questions. Thanks

Mahadevan 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, (e-mail address removed) 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
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
---
:
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. Thankyou
Swamy- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[mahadevan.swamy]

Max,

My formula is this: -
=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)

and your formula is this: -

=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)))

What happened to the CONCATENATE function?

 

[mahadevan.swamy]

There is another formula that requires the same technique and I have a
poor knowledge of the offset function.

This is the formula in cell d6 : -

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

This formula too needs to skip 5 columns in book 3. I would appreciate
if you can use the same technique on this formula too. Thanks

Swamy

 

[Max]

Simplified : CONCATENATE(LEFT(C$2, 2),$A6)
to: LEFT($C2,2)&$A6
in my expression

The ampersand operator "&" achieves the same purpose
but it's much shorter and easier to type

---
Max,

My formula is this: -
=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)

and your formula is this: -

=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)))

What happened to the CONCATENATE function?

 

[Max]

Hi Max,
Thanks for your solution. It worked.

Good to hear that. This closes your orig. post.

I have another problem ..

Suggest that you put in a fresh posting for new queries
(stick to one query per post)

---

 

[mahadevan.swamy]

Thanks for your help.

There is another formula that requires the same technique and I have
a
poor knowledge of the offset function.

This is the formula in cell d6 : -

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

This formula too needs to skip 5 columns in book 3. I would
appreciate
if you can use the same technique on this formula too. Thanks


Swamy

 

[Max]

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

 

[Max]

in cell u6,

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

.. But from U6 to AK6 , it should skip 5 columns in book 3

From cells U6 to AK6, the formula must remain constant
but should have skipped 5 columns in book 3.

I know that I have to use the same approach ..

Yes, same approach.
Since it's to skip-increment in the same manner as earlier,
just amend the OFFSET's fixed ranges to suit in the starting cell
here, ie in U6

Put this into U6:
=IF(MOD(COLUMN(A1)-1,2)=1,"",SUMIF(OFFSET('[Book3.xls]Line Returns
(Internal)'!$J$4:$J$300,,INT((COLUMN(A1)-1)/2)*5),LEFT($C2,2)&
$A6,OFFSET('[Book3.xls]Line Returns (Internal)'!$G$4:$G
$300,,INT((COLUMN(A1)-1)/2)*5)))
Copy U6 across to AK6

Note: Do not change the COLUMN(A1) bit in the starting cell.

---