Referencia horizontal al autocompletado de fórmula vertical - Microsoft Excel 2007

Question

I'm trying to make a employee check-in chart for the year with one viewing tab, and 12 reference tabs for data entry. Within a given month, the dates are horizontal, D4:AG4, and the employees check in, represented with a '1', marked vertically in each day column. I then made a formula on the viewing tab, Sheet1, for each day of that month to bring back the sum of all the employees checked in that day, like this:

A1=Sum(Sheet2!D4:D53)
A2=Sum(Sheet2!E4:E53)

The problem is, each entry is for a day, and I'm trying not to enter those formulas 365 times for the whole year. Is there a function I can use to autofill downwards without it changing the reference cells vertically too? This is what using the auto fill does right now:

A3=Sum(Sheet2!F5:F54)
A4=Sum(Sheet2!F6:F55)

Answer 1

Type

=SUM(OFFSET(Sheet2!$D$4,,ROW()-1,50))

in Sheet1!A1 and drag/copy it down to A30.

If all your monthly data are organized in consequently numbered sheets Sheet2...Sheet13, then you can use this formula

=SUM(OFFSET(INDIRECT(ADDRESS(4,4,,,"Sheet"&(COLUMN()+1))),,ROW()-1,50))

in A1 and copy it to fill the whole range A1:L30 to get all months processed.

This is an example with random data:

Answer 2

To do this, you should use the $ when using cell references. Putting a $ before the Cell letter holds that cell letter when autofilling/dragging. Putting a $ before the cell number holds that cell number when autofilling/dragging. I do not believe however that you can increase

En su caso, una manera fácil de hacerlo es primero autocompletar su fórmula en 53 espacios horizontales (de la celda B a la BB, explicaré por qué más adelante). Luego, copie esas 53 celdas, luego, cuando vaya a pegar en A1, asegúrese de pegar -> especial -> transponer. Esto publicará la celda horizontal verticalmente. Luego puedes continuar y eliminar las celdas horizontales que creaste con la fórmula. La razón por la que digo que lo haga desde la celda B hasta la BB es que si lo hizo en la celda A y luego intentó pegarlo, no le permitirá y regresará con un error. Esta es sólo una solución entre muchas.