ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Skip cells and copy formulas (https://www.excelbanter.com/excel-discussion-misc-queries/130363-skip-cells-copy-formulas.html)

[email protected]
Skip cells and copy formulas
 
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

Bernie Deitrick
Skip cells and copy formulas
 
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



wrote in message
ups.com...
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

[email protected]
Skip cells and copy formulas
 
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.



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

wrote in message

ups.com...



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 -

Bernie Deitrick
Skip cells and copy formulas
 
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


wrote in message ups.com...
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.



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

wrote in message

ups.com...



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 -

[email protected]
Skip cells and copy formulas
 
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



On Feb 13, 12:08 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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



wrote in oglegroups.com...
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.

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

wrote in message

roups.com...

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 -

Bernie Deitrick
Skip cells and copy formulas
 
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


wrote in message
oups.com...
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



On Feb 13, 12:08 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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



wrote in
oglegroups.com...
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.

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

wrote in message

roups.com...

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 -


All times are GMT +1. The time now is 05:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com