Skip cells and copy formulas

N

nsharish20

I want to copy formulas horizontally and in the formula, it want it to
skip 5 columns and then calculate. Is there a way to do this?

For example, in one cell I have

=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 the next adjacent cell, I have

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

So i want to copy formulas skipping 5 columns in the exterior workbook
(book 3). I would appreciate if anyone has some good solutions.


Thanks in advance
 
B

Bernie Deitrick

N,

The general idea is to tie your offset to the column of the formula - for
example, if your first formula is in cell E2:

=SUMIF(OFFSET('[Book3.xls]Line Returns
(Internal)'!$E$4:$E$300,0,(COLUMN()-COLUMN($E$2))*5), CONCATENATE(LEFT(C$3,
2),$A6),OFFSET('[Book3.xls]Line Returns
(Internal)'!$B$4:$B$300,0,(COLUMN()-COLUMN($E$2))*5))

Then copy to the right, and it should work...

HTH,
Bernie
MS Excel MVP
 
N

nsharish20

Hi,

Thank you for your solution. I used the formula that you gave me and
it didnt work. I get the same result like: -

in cell C6, I have

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!E$4:E$300,0,
(COLUMN()-COLUMN(C$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!B$4:B$300,0,(COLUMN()-
COLUMN(C$6))*5))

in the adjacent cell E6 (Cells - blank - Cells) , I have

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!G$4:G$300,0,
(COLUMN()-COLUMN(E$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!D$4:D$300,0,(COLUMN()-
COLUMN(E$6))*5)

Whethere i put a $ or not, it gives me the same result.



N,

The general idea is to tie your offset to the column of the formula - for
example, if your first formula is in cell E2:

=SUMIF(OFFSET('[Book3.xls]Line Returns
(Internal)'!$E$4:$E$300,0,(COLUMN()-COLUMN($E$2))*5), CONCATENATE(LEFT(C$3,
2),$A6),OFFSET('[Book3.xls]Line Returns
(Internal)'!$B$4:$B$300,0,(COLUMN()-COLUMN($E$2))*5))

Then copy to the right, and it should work...

HTH,
Bernie
MS Excel MVP




I want to copy formulas horizontally and in the formula, it want it to
skip 5 columns and then calculate. Is there a way to do this?
For example, in one cell I have
=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 the next adjacent cell, I have
=SUMIF('[Book3.xls]Line Returns (Internal)'!$J$4:$J$300,
CONCATENATE(LEFT(C$3, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$G
$4:$G$300)
So i want to copy formulas skipping 5 columns in the exterior workbook
(book 3). I would appreciate if anyone has some good solutions.
Thanks in advance- Hide quoted text -

- Show quoted text -
 
B

Bernie Deitrick

Change the C$6 to $C$6

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!E$4:E$300,0,
(COLUMN()-COLUMN($C$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!B$4:B$300,0,(COLUMN()-
COLUMN($C$6))*5))

The formula in cell C6 should give you the same result as this version, without the offset function:

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

HTH,
Bernie
MS Excel MVP


Hi,

Thank you for your solution. I used the formula that you gave me and
it didnt work. I get the same result like: -

in cell C6, I have

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!E$4:E$300,0,
(COLUMN()-COLUMN(C$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!B$4:B$300,0,(COLUMN()-
COLUMN(C$6))*5))

in the adjacent cell E6 (Cells - blank - Cells) , I have

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!G$4:G$300,0,
(COLUMN()-COLUMN(E$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!D$4:D$300,0,(COLUMN()-
COLUMN(E$6))*5)

Whethere i put a $ or not, it gives me the same result.



N,

The general idea is to tie your offset to the column of the formula - for
example, if your first formula is in cell E2:

=SUMIF(OFFSET('[Book3.xls]Line Returns
(Internal)'!$E$4:$E$300,0,(COLUMN()-COLUMN($E$2))*5), CONCATENATE(LEFT(C$3,
2),$A6),OFFSET('[Book3.xls]Line Returns
(Internal)'!$B$4:$B$300,0,(COLUMN()-COLUMN($E$2))*5))

Then copy to the right, and it should work...

HTH,
Bernie
MS Excel MVP




I want to copy formulas horizontally and in the formula, it want it to
skip 5 columns and then calculate. Is there a way to do this?
For example, in one cell I have
=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 the next adjacent cell, I have
=SUMIF('[Book3.xls]Line Returns (Internal)'!$J$4:$J$300,
CONCATENATE(LEFT(C$3, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$G
$4:$G$300)
So i want to copy formulas skipping 5 columns in the exterior workbook
(book 3). I would appreciate if anyone has some good solutions.
Thanks in advance- Hide quoted text -

- Show quoted text -
 
N

nsharish20

It still didnt work.I would like to make my problem more clear.

I have an exterior workbook (Book 3) which is linked to the current
workbook (Report 4) which I am working on. In this workbook, I enter
formulas requesting data to be taken from book 3 and excute a
function.

So when I enter formulas in report 4, starting from the first cell, I
want it to skip 5 columns in book 3 before being copied on to the next
cell. This is what I am looking for: -

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

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

I dont want to manually correct the formulas by enter E, J, O,
T....B,G, L, Q as i have more columns to fill and it will be hectic to
go to each and every cell and fill it up.

You have the right approach of using OFFSET function but the result I
am getting currently is: -

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!E$4:E$300,0,
(COLUMN()-COLUMN($C$6))*5), CONCATENATE(LEFT($C$2, 2),
$A6),OFFSET('[Book3.xls]Line Returns (Internal)'!B$4:B$300,0,(COLUMN()-
COLUMN($C$6))*5))

on the next cell

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!G$4:G$300,0,
(COLUMN()-COLUMN($C$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!D$4:D$300,0,(COLUMN()-
COLUMN($C$6))*5))

It is following like E, G, I ...... B, D, F. It is just skipping two
columns in Book 3.

Thank you for your help

Swamy



Change the C$6 to $C$6

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!E$4:E$300,0,
(COLUMN()-COLUMN($C$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!B$4:B$300,0,(COLUMN()-
COLUMN($C$6))*5))

The formula in cell C6 should give you the same result as this version, without the offset function:

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

HTH,
Bernie
MS Excel MVP



Thank you for your solution. I used the formula that you gave me and
it didnt work. I get the same result like: -
in cell C6, I have
=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!E$4:E$300,0,
(COLUMN()-COLUMN(C$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!B$4:B$300,0,(COLUMN()-
COLUMN(C$6))*5))
in the adjacent cell E6 (Cells - blank - Cells) , I have
=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!G$4:G$300,0,
(COLUMN()-COLUMN(E$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!D$4:D$300,0,(COLUMN()-
COLUMN(E$6))*5)
Whethere i put a $ or not, it gives me the same result.
N,
The general idea is to tie your offset to the column of the formula - for
example, if your first formula is in cell E2:
=SUMIF(OFFSET('[Book3.xls]Line Returns
(Internal)'!$E$4:$E$300,0,(COLUMN()-COLUMN($E$2))*5), CONCATENATE(LEFT(C$3,
2),$A6),OFFSET('[Book3.xls]Line Returns
(Internal)'!$B$4:$B$300,0,(COLUMN()-COLUMN($E$2))*5))
Then copy to the right, and it should work...
HTH,
Bernie
MS Excel MVP

I want to copy formulas horizontally and in the formula, it want it to
skip 5 columns and then calculate. Is there a way to do this?
For example, in one cell I have
=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 the next adjacent cell, I have
=SUMIF('[Book3.xls]Line Returns (Internal)'!$J$4:$J$300,
CONCATENATE(LEFT(C$3, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$G
$4:$G$300)
So i want to copy formulas skipping 5 columns in the exterior workbook
(book 3). I would appreciate if anyone has some good solutions.
Thanks in advance- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -
 
B

Bernie Deitrick

I overlooked other cell references that you changed from my original post:

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!$E$4:$E$300,0, (COLUMN()-COLUMN($C$6))*5),
CONCATENATE(LEFT($C$2, 2),$A$6), OFFSET('[Book3.xls]Line Returns
(Internal)'!$B$4:$B$300,0,(COLUMN()- COLUMN($C$6))*5))

Please note that EVERY cell reference should be absolute: $Column$Row:$Column$Row.... and the $C$6
should be the address of the cell where you first enter this formula.

HTH,
Bernie
MS Excel MVP


It still didnt work.I would like to make my problem more clear.

I have an exterior workbook (Book 3) which is linked to the current
workbook (Report 4) which I am working on. In this workbook, I enter
formulas requesting data to be taken from book 3 and excute a
function.

So when I enter formulas in report 4, starting from the first cell, I
want it to skip 5 columns in book 3 before being copied on to the next
cell. This is what I am looking for: -

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

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

I dont want to manually correct the formulas by enter E, J, O,
T....B,G, L, Q as i have more columns to fill and it will be hectic to
go to each and every cell and fill it up.

You have the right approach of using OFFSET function but the result I
am getting currently is: -

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!E$4:E$300,0,
(COLUMN()-COLUMN($C$6))*5), CONCATENATE(LEFT($C$2, 2),
$A6),OFFSET('[Book3.xls]Line Returns (Internal)'!B$4:B$300,0,(COLUMN()-
COLUMN($C$6))*5))

on the next cell

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!G$4:G$300,0,
(COLUMN()-COLUMN($C$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!D$4:D$300,0,(COLUMN()-
COLUMN($C$6))*5))

It is following like E, G, I ...... B, D, F. It is just skipping two
columns in Book 3.

Thank you for your help

Swamy



Change the C$6 to $C$6

=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!E$4:E$300,0,
(COLUMN()-COLUMN($C$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!B$4:B$300,0,(COLUMN()-
COLUMN($C$6))*5))

The formula in cell C6 should give you the same result as this version, without the offset
function:

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

HTH,
Bernie
MS Excel MVP



Thank you for your solution. I used the formula that you gave me and
it didnt work. I get the same result like: -
in cell C6, I have
=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!E$4:E$300,0,
(COLUMN()-COLUMN(C$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!B$4:B$300,0,(COLUMN()-
COLUMN(C$6))*5))
in the adjacent cell E6 (Cells - blank - Cells) , I have
=SUMIF(OFFSET('[Book3.xls]Line Returns (Internal)'!G$4:G$300,0,
(COLUMN()-COLUMN(E$6))*5), CONCATENATE(LEFT($C$2, 2),$A6),
OFFSET('[Book3.xls]Line Returns (Internal)'!D$4:D$300,0,(COLUMN()-
COLUMN(E$6))*5)
Whethere i put a $ or not, it gives me the same result.
On Feb 12, 4:38 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:
N,
The general idea is to tie your offset to the column of the formula - for
example, if your first formula is in cell E2:
=SUMIF(OFFSET('[Book3.xls]Line Returns
(Internal)'!$E$4:$E$300,0,(COLUMN()-COLUMN($E$2))*5), CONCATENATE(LEFT(C$3,
2),$A6),OFFSET('[Book3.xls]Line Returns
(Internal)'!$B$4:$B$300,0,(COLUMN()-COLUMN($E$2))*5))
Then copy to the right, and it should work...
HTH,
Bernie
MS Excel MVP
I want to copy formulas horizontally and in the formula, it want it to
skip 5 columns and then calculate. Is there a way to do this?
For example, in one cell I have
=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 the next adjacent cell, I have
=SUMIF('[Book3.xls]Line Returns (Internal)'!$J$4:$J$300,
CONCATENATE(LEFT(C$3, 2),$A6),'[Book3.xls]Line Returns (Internal)'!$G
$4:$G$300)
So i want to copy formulas skipping 5 columns in the exterior workbook
(book 3). I would appreciate if anyone has some good solutions.
Thanks in advance- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top