Use of OFFSET function

M

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
 

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