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)
Best Answer
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.