# Horizontal reference to vertical formula autofill - Microsoft Excel 2007

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)
``````
• I'm sure you are looking for `\$`. Put `\$` before the row number so it won't be increased/decreased when you autofill, ex: `Sheet2!F\$4:F\$53` based on your first example. – Vylix May 26 '17 at 3:39

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

In your case, an easy way to do this is to first autofill your formula across horizontal 53 spaces (Cell B through BB, I'll explain why later). Next, copy those 53 cells, then when you go to paste in A1, make sure to paste -> special -> transpose. This will post the horizontal cell vertically. You can then go ahead and delete the horizontal cells that you created with the formula. The reason I say do it in cell B through BB, if you did it in cell A and then tried pasting over it, it would not let you and would come back with an error. This is just one solution of many.

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: