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!

Dotard

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

Nutwood

I tried the formula you suggested, but neither the formula changes on the next rows, neither I am getting the values of the columns. Maybe I didn't express myself correctly: I actually need to retrieve values from the columns on the right, when I drag it down.. – Dotard

... I don't understand... Your formula was a SUM function??? - As for the formula, it wouldn't change physically, the indirect function would simply look one more column to the right with each additional row you dragged it down.... AGAIN, my formula assumes you started with it in row 1 to get the column H values... Did you do that????? – Nutwood

To re-explain - put =OFFSET(Banco!$G$5,0,ROW()) in say, cell A1, it will reference cell H5, if you now drag this formula down to cell B1, it will reference cell I5, etc.... – Nutwood

My formula is exactly as I posted in the question, so yes it is a SUM function. All I need is to drag down that formula and each row must give me the value of the columns to the right, not the rows below (i.e. instead of getting H$5, H$6, H$7, I need to get H$5, I$5, J$5). Is it possible? – Dotard

The formula I gave you will do EXACTLY that, then... Just start in row 1 and try it... – Nutwood

Ok ok, got that. But if I use that on cell E5 (where I really want to use that) it just doesn't work. WHY? – Dotard

You have to update the row() part.... just change every place you see row() to row()-4 since now you are starting in the 5th row, not the first... – Nutwood

If you use ROW() function you can have problems if you delete rows (the formula result will change). Using ROWS can be more robust, e.g. if you want the first result in E5 try using =OFFSET(Banco!$H$5,0,ROWS(E$5:E5)-1).....also you could simplify the longer version by summing the whole range and subtracting row 7, e.g. =SUM(OFFSET(Banco!$H$5:$H$11,0,ROWS(E$5:E5)-1))-OFFSET(Banco!$H$7,0,ROWS(E$5:E5)-1) – Lytle

Answer 2

Use a combination of the OFFSET and the COUNTA function. The OFFSET function will give the offset of COUNTA columns each time you go down a row. Make the counta function count the number of rows above the row that you're dragging the entire function into (aka each time you drag the function to an extra row, it will add 1)

Goldschmidt