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
$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: -
=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
$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...
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 -
