Networkdays - Formula showing reference needs to clear

[shiraz50]

Hi

In column BN i need the public holidays for 2023 to be excluded from the nework days . It does not allow me to copy the public holidays all the way to column 18 from the second sheet.

Formula in Column BN

=IF(ISBLANK(U5);"";IF(AND(ISNUMBER(U5)=FALSE;ISNUMBER(X5)=FALSE);"Not sent as yet";IF(AND(ISNUMBER(U5)=TRUE;ISNUMBER(X5)=TRUE);NETWORKDAYS(U5;X5;'C:\Users\502766436\AppData\Local\Box\Box Edit\Documents\4PqXdAP0U0qp503ktNccfA==\[GE Parts Logistics Tracker - WE10.xlsx]Public Holidays'!$A$5:$A$16);IF(AND(ISNUMBER(U5)=TRUE;ISNUMBER(X5)=FALSE;ISNUMBER(BM5)=TRUE);NETWORKDAYS(U5;$BL$1;'C:\Users\502766436\AppData\Local\Box\Box Edit\Documents\4PqXdAP0U0qp503ktNccfA==\[GE Parts Logistics Tracker - WE10.xlsx]Public Holidays'!$A$5:$A$18)-1))))

Even column BT has the same issue. Please help.

Formula in Column BT

=IF(ISBLANK(U6);"";IF(AND(ISNUMBER(U6)=FALSE;ISNUMBER(Y6)=FALSE);"Not sent as yet";IF(AND(ISNUMBER(U6)=TRUE;ISNUMBER(Y6)=TRUE);NETWORKDAYS(U6;Y6;'C:\Users\502766436\Documents\Spares\weekly tracker\[GE Parts Logistics Tracker - WE47 2020.xlsx]Public Holidays'!$A$5:$A$16);IF(AND(ISNUMBER(U6)=TRUE;ISNUMBER(Y6)=FALSE;ISNUMBER(BS6)=TRUE);NETWORKDAYS(U6;$BL$1;'C:\Users\502766436\Documents\Spares\weekly tracker\[GE Parts Logistics Tracker - WE47 2020.xlsx]Public Holidays'!$A$5:$A$18)-1))))

 

[jasonb75]

There is nothing that stands out as being wrong in your formulas, but often formulas mean very little to people that don't have the data to go with them.

Could you elaborate on what you mean here please?

It does not allow me to copy the public holidays all the way to column 18 from the second sheet.

Are we to assume that you mean Row 18 rather than column 18 (which would be column R).

Are you getting an error message (if so, what is the message) or an incorrect result (in which case we would need to see some examples to understand why it is not working).

 

[shiraz50]

There is nothing that stands out as being wrong in your formulas, but often formulas mean very little to people that don't have the data to go with them.

Could you elaborate on what you mean here please?

Are we to assume that you mean Row 18 rather than column 18 (which would be column R).

Are you getting an error message (if so, what is the message) or an incorrect result (in which case we would need to see some examples to understand why it is not workinz

Hi Jason

The error message is #REF

Row18 is on another sheet

 

[jasonb75]

The #REF error means that the formula is trying to look at a sheet that doesn't exist.

Looking at your formula, it could only be caused by an error in the filename of the weekly tracker sheet.

There are some formulas that only work when the source (weekly tracker) sheet is open. I don't believe that this is the case with your formula but if it works when you open the weekly tracker sheet then that will be the cause and the solution.

 

[shiraz50]

The #REF error means that the formula is trying to look at a sheet that doesn't exist.

Looking at your formula, it could only be caused by an error in the filename of the weekly tracker sheet.

There are some formulas that only work when the source (weekly tracker) sheet is open. I don't believe that this is the case with your formula but if it works when you open the weekly tracker sheet then that will be the cause and the solution.

Thanks . It works the formula in blue has been changed but i need to change this one in red as weel. When i apply the same as indicated in Blue it comes out as # VALUE. Please advise ?

=IF(ISBLANK(U5);"";IF(AND(ISNUMBER(U5)=FALSE;ISNUMBER(X5)=FALSE);"Not sent as yet";IF(AND(ISNUMBER(U5)=TRUE;ISNUMBER(X5)=TRUE);NETWORKDAYS(U5;X5;'C:\Users\502766436\AppData\Local\Box\Box Edit\Documents\4PqXdAP0U0qp503ktNccfA==\[GE Parts Logistics Tracker - WE10.xlsx]Public Holidays'!$A$5:$A$16);IF(AND(ISNUMBER(U5)=TRUE;ISNUMBER(X5)=FALSE;ISNUMBER(BM5)=TRUE);NETWORKDAYS(U5;$BL$1;'Public Holidays'!A4:A18)-1))))

Please advise

 

[jasonb75]

Looking at the screen capture in your earlier reply I think it should be

Public Holidays'!$A$5:$A$18

A4 contains the word "date" rather than an actual date so that will cause the error.

Also you had the range as relative (no $ symbols) which means that it will change if you drag the formula down to more rows, A5:A18 becomes A6:A19 then A7:A20 and so on.

 

[shiraz50]

Can i send you the report on email . I am battling to add in the attachment

 

[jasonb75]

You can't add attachments to posts on the forum, it is preferred if you could either use the XL2BB add in (link at the bottom of my replies) or as a last resort upload the file to a sharing service and post a link to it in your thread.

Sharing via email goes against forum etiquette (and I think it's against the rules as well).

 

[shiraz50]

Thanks for all your support

 

[Fluff]

@shiraz50 in future please do mark mark a post as the solution when it does not contain one. Thanks