Drag down formula and change COLUMN references instead of ROWS

Question

I have this formula in Excel, in row E5:

=SUM(Banco!H$5;Banco!H$6;Banco!H$8;Banco!H$9;Banco!H$10;Banco!H$11)

I need it to change the COLUMN references instead of ROWS when I drag it down (basically behave like I was dragging it across)... For example:

=SUM(Banco!I$5;Banco!I$6;Banco!I$8;Banco!I$9;Banco!I$10;Banco!I$11)
=SUM(Banco!J$5;Banco!J$6;Banco!J$8;Banco!J$9;Banco!J$10;Banco!J$11)
=SUM(Banco!K$5;Banco!K$6;Banco!K$8;Banco!K$9;Banco!K$10;Banco!K$11)

Any clues?

Thanks a lot!

Answer 1

... Use the offset function.

For example - Suppose you had a formula in row 1 and you wanted to reference Banco!H5, you could do something like:

=OFFSET(Banco!$G$5,0,ROW())

Now, as you drag it down, it will offset by the number of rows down you go.

So your new formula would look as follows:

=SUM(OFFSET(Banco!$G$5,0,ROW()),OFFSET(Banco!$G$6,0,ROW()),OFFSET(Banco!$G$8,0,ROW()),OFFSET(Banco!$G$9,0,ROW()),OFFSET(Banco!$G$10,0,ROW()),OFFSET(Banco!$G$11,0,ROW()))

Again, this assumes you are pasting this formula in row 1 (I know it's ugly, but you specified specific cells, so you have to specify each one separately)

Hope this makes sense