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
1		Mark	Chris	Pete
2			2-8-2016
3			4-8-2016
4						Name	Start	end	Formula
5						Chris	2-8-2016	4-8-2016	1
6						Pete	2-8-2016	4-8-2016
7
8
9

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I5 =NETWORKDAYS(G5,H5,INDIRECT(LOOKUP(F5,$B$1:$D$1))) <tbody> </tbody>

<tbody>
</tbody>

 

[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?