10

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!

Marcelo
  • 351
  • 1
  • 5
  • 18

2 Answers2

11

... 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

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • 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.. – Marcelo Nov 12 '12 at 18:13
  • ... 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????? – John Bustos Nov 12 '12 at 18:18
  • 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.... – John Bustos Nov 12 '12 at 18:21
  • 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? – Marcelo Nov 12 '12 at 18:24
  • The formula I gave you will do EXACTLY that, then... Just start in row 1 and try it... – John Bustos Nov 12 '12 at 18:28
  • 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? – Marcelo Nov 12 '12 at 18:29
  • 1
    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... – John Bustos Nov 12 '12 at 18:30
  • 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)` – barry houdini Nov 12 '12 at 21:35
0

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)

Alexander Khvatov
  • 1