| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Skip and copy formulas | Excel Worksheet Functions | |||
| how do I skip blank cells when writing formulas | Excel Worksheet Functions | |||
| How do I copy information and skip hidden cells? | Excel Discussion (Misc queries) | |||
| I want to copy a formula n Excel but skip any blank cells | Excel Worksheet Functions | |||
| How do I copy cells and skip lines? | Excel Discussion (Misc queries) | |||
Linear Mode
