Networkdays - cell determines holiday list/range to use

[stonelaw119]

Please help

I want use Networkdays to determine how many hours per workday a team member will need to spend on each task. The problem I am having is I need to account for the team members days off also.

I have created a named list for each team member (using their name) that has all of their individual days off. The problem I am having is I can not figure out how to change the "Holiday" list in the NETWORKDAYS formula to match the team members name. I know it works when I manually update the fields with the list name but there is hundreds of rows of data so I am hoping there is a formula that allows me to reference a cell (Team Member's Name) to determine which list to pick. Any ideas on how I can accomplish this?


Team Member Start ETD Hours
George | 7/1/2016 | 9/1/2016 | 30
Mike | 7/10/2016 | 9/20/2016 | 40
Bill | 7/20/2016 | 8/3/2016 | 10


Your help would be appreciated!

 

[jorismoerings]

Hi,

Check if this is what your after. I also created named ranges for the list of days of .

Excel 2013 32 bit

	A	B	C	D	E	F	G	H	I	J
Mark	Chris	Pete
Name	Start	end	Formula
Chris
Pete

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"]2-8-2016[/TD]
[TD="bgcolor: #9BC2E6, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"]4-8-2016[/TD]
[TD="bgcolor: #9BC2E6, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"][/TD]
[TD="bgcolor: #9BC2E6, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"][/TD]
[TD="bgcolor: #9BC2E6, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2-8-2016[/TD]
[TD="align: right"]4-8-2016[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"][/TD]
[TD="bgcolor: #9BC2E6, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2-8-2016[/TD]
[TD="align: right"]4-8-2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"][/TD]
[TD="bgcolor: #9BC2E6, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"][/TD]
[TD="bgcolor: #9BC2E6, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"][/TD]
[TD="bgcolor: #9BC2E6, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell
[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I5
[/TH]
[TD="align: left"]=NETWORKDAYS(G5,H5,INDIRECT(LOOKUP(F5,$B$1:$D$1)))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

=NETWORKDAYS(m,n,INDIRECT(name))

where m, n are dates (m<=n) and name stands for a list housing the holiday dates which hold for the team member with that name.

 

[stonelaw119]

Thanks your your replies. I had been trying those formats and it was not working for me. It had me totally stumped!!!

I finally found a thread where someone had the same issue. We both were trying to reference dynamic lists (offset/count). The thread basically said that it was an Excel error and that I needed to add in Function below in to correct the problem.

Function BINDIRECT(strRange As String) As Range
Application.Volatile True
Set BINDIRECT = Range(strRange)
End Function

Once I had added the Function in, everything started working. YAY!

Again, thanks for your replies, as it should have worked but excel instead did not want to place nice.

 

[Aladin Akyurek]

Would you provide the data sample where the INFDIRECT use fails?