0

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
0

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.

0

Type

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

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

enter image description here

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.