NETWORKDAYS and AUTOFILL

[schumajb]

I need to use excel to calculate the number of work days not includin
holidays it takes us to complete certain tasks between two dates. I a
using this formula which is working perfect if I manually type it o
each line. The HOLIDAYS are entered on a separate tab labled HOLIDAY
in cells A1-A8.

=NETWORKDAYS(B2,D2,Holidays!A1:A8)-1

When I use Auto fill it isn't working for me. How can I get th
formula to not change when using autofill?

For example when using autofill I want my additional formulas to rea
like this for each line

1 =NETWORKDAYS(B2,D2,Holidays!*A1:A8* )-1
2 =NETWORKDAYS(B3,D3,Holidays!*A1:A8* )-1
3 =NETWORKDAYS(B4,D4,Holidays!*A1:A8* )-1
4 =NETWORKDAYS(B5,D5,Holidays!*A1:A8* )-1
5 =NETWORKDAYS(B6,D6,Holidays!*A1:A8* )-1

But instead they read like this and give me incorrect results.

1 =NETWORKDAYS(B2,D2,Holidays!*A1:A8* )-1
2 =NETWORKDAYS(B3,D3,Holidays!*A2:A9* )-1
3 =NETWORKDAYS(B4,D4,Holidays!*A3:A10* )-1
4 =NETWORKDAYS(B5,D5,Holidays!*A4:A11* )-1
5 =NETWORKDAYS(B6,D6,Holidays!*A5:A12* )-1

Notice it is changing the Holiday Cells which I want to be A1:A8 o
each line.

I need to have at least 600 lines so i do not want to manually type i
each formula or have to edit each line.

Please help! My email is (e-mail address removed) Thanks!!!!

 

[Alan]

You need to make A1:A8 absolute as opposed to relative, you will find more
in Help
A1 is relative, ie will change to A2, A3 etc if dragged down,
$A$1 is absolute and will stay as A1 when dragged,
=NETWORKDAYS(B2,D2,Holidays!$A$1:$A$8)-1
and drag down,
Regards,

 

[Norman Harker]

Hi Schumajb!

Use:
=NETWORKDAYS(B2,D2,Holidays!$A$1:$A$8)-1

Or
Name the range (eg) MyHolidays and use that.

 

[Frank Kabel]

Hi
try:
=NETWORKDAYS(B2,D2,Holidays!$A$1:$A$8)-1

and have a look at 'absolute / relative references' in the Excel help

 

[schumajb]

Thanks everyone. That worked perfectly