Drag down formula and change COLUMN references instead of ROWS
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
... 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