Autofill Increment a Reference to Another Worksheet

[Ben Jitima]

We have a weekly schedule that calculates the number of hours each
employee has as you make their schedule. However it is very long and
when fit to a single page it is hard to read.

I made a nicer printable layout on another worksheet and started
referencing the cells in the other worksheet so we do not have to type
the schedule out twice. The cells are laid out differently, so when
it references the other worksheet, it increments by threes. When I
try to autofill, it does some quirky things depending on how many
cells I try to autofill.

For example, Sunday is set up as follows

EMP1 Time in - E6
EMP1 Time out - E7

EMP2 Time in - E9
EMP2 Time out - E10

EMP3 Time in - E12
EMP3 Time out - E13 etc

The printable schedule has EMP1 Time in - B4 Time out - D4

So when it prints, it looks like:

Employee Name | 12:00 PM | - | 8:00 PM |
with | denoting cells

The formula I am using is:
=IF('Weekly Time Sheet'!E6="","",'Weekly Time Sheet'!E6)
=IF('Weekly Time Sheet'!E9="","",'Weekly Time Sheet'!E9)
=IF('Weekly Time Sheet'!E12="","",'Weekly Time Sheet'!E12) etc

When I autofill it does one of two things. It either repeats the
highlighted cells over and over, or it increments them at numbers in
between (Such as E7, E10, E13 in the formula example). However, to
avoid entering every formula for every employee for every day, I would
really like to get the autofill to work the way I thought it should.

Does anyone know how I could go about doing it, or a different or
simpler way of going about it? If I wasn't clear enough about how the
worksheets are laid out, I would be glad to send it out.

Thanks!
Ben

 

[Ben Jitima]

Also, I forgot to mention, we are currently using Excel 2003.
However, I have access to a computer with Excel 2007 and I plan on
trying it on that computer tomorrow to see if there is a difference.

 

[Pete_UK]

You can use INDIRECT in your formula to ensure the numbers increment
in 3s as you copy them down.

It's very late here, so I can't advise you further, but if nobody else
has come forward I'll pick this up again tomorrow.

Pete

 

[Pete_UK]

Consider this formula:

=INDIRECT("'Weekly time sheet'!E"&(3*ROW(A1)+3))

If this is copied down it becomes:

=INDIRECT("'Weekly time sheet'!E"&(3*ROW(A2)+3))
=INDIRECT("'Weekly time sheet'!E"&(3*ROW(A3)+3))

and so on. Now, ROW(A1) returns 1, so the calculation returns 3*1+3 =
6. In the second formula the calculation returns 3*2+3 = 9, and the
third one is 3*3+3 = 12.

INDIRECT enables you to build up calculated cell references in this
way, so that you can obtain data from rows 6, 9, 12 etc on consecutive
rows.

Hope this helps.

Pete

 

[Roger Govier]

Hi Ben

You could use the Index function coupled with Row() to achieve this.
=ROW(E2) would return a value of 2
When copied down it would become =ROW(E3) with a value of 3 and so on

So make your formula
=IF(INDEX('Weekly Time Sheet'!E:E,ROW(E2)*3)="","",
INDEX('Weekly Time Sheet'!E:E,ROW(E2)*3))
and copy down.