INDIRECT Function and Autofill
 

Hi folks

After a bit of searching the groups, I found some tips on something I
want to do, but it's not quite working out.

I have weekly timesheets containing about 20 staff, that work out
salaries, each sheet is named week 1, week 2 etc.... to week 52
Part of the sheet sums how many days holiday taken that week (if any),
and days off sick.

Finally, I have a separate sheet on which I want to copy the days
taken on leave, and subtract them from their annual entitlement,

So, in Column A and B are foreame and surname, column C has the annual
leave entitlement. Cells D2 to BC2 have the lables week 1 etc.
The cell D3 to BC3 have the first staff member, and I used the
following to copy the value from each sheet.
=INDIRECT(""'&D$2&"'!AK8") - this went in D3, and I used the Autofill
to copy this across the row.
However, if I try to Autofill down the column, the value !AK8 doesn't
change.
Is there a way round this or did I do something wrong.

Thanks
Neil

Maybe

=INDIRECT(""'&D$2&"'!AK"&ROW(A8))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Neil Grantham" wrote in message
om...
Hi folks

After a bit of searching the groups, I found some tips on something I
want to do, but it's not quite working out.

I have weekly timesheets containing about 20 staff, that work out
salaries, each sheet is named week 1, week 2 etc.... to week 52
Part of the sheet sums how many days holiday taken that week (if any),
and days off sick.

Finally, I have a separate sheet on which I want to copy the days
taken on leave, and subtract them from their annual entitlement,

So, in Column A and B are foreame and surname, column C has the annual
leave entitlement. Cells D2 to BC2 have the lables week 1 etc.
The cell D3 to BC3 have the first staff member, and I used the
following to copy the value from each sheet.
=INDIRECT(""'&D$2&"'!AK8") - this went in D3, and I used the Autofill
to copy this across the row.
However, if I try to Autofill down the column, the value !AK8 doesn't
change.
Is there a way round this or did I do something wrong.

Thanks
Neil