I've created a format that basically allocates the time assigned by day but in order to do so I had to use 365 columns. Thus for 30,000 tasks its a total of 10.1 million calculations. I've noticed that after about the first 2000 lines the sheet errors ans says that excel has insufficient resources. As a work around I've parted out the overall list into increments of 2000 or less but now I have several files. Perhaps my formulas are inefficient? Below is what it looks like (first row is the headers, second row is example data, 3rd row is the formulas used in each column):

Blank Column | Blank Column | Task Name | Task # | Reference | Project Reference | Location | # of Items | Time Frame | Classification | Hours to Complete | Task Type | Employee Assigned | Start Date | End Date | # of Days | Location of Assigned | Working Days | Hours/Day | Blank Column | 1/1/19 | 1/2/19 |

Task A | 1 | Sales | Process Improvement | USA | 40 | Q1 | X1 | 3 | Test | Mark | 1/1/19 | 1/5/19 | 5 | USA | 5 | 1 | 1 | 1 | |||

Harded Coded Data | Harded Coded Data | Harded Coded Data | Harded Coded Data | Harded Coded Data | Harded Coded Data | Harded Coded Data | Harded Coded Data | Harded Coded Data | Harded Coded Data | Harded Coded Data | Harded Coded Data | Harded Coded Data | =O198-N198+1 | =IFERROR(VLOOKUP($M:$M,'Resource Roster'!$B:$E,4,0),"N/A") | =IFERROR(IF(OR($Q198="KPMG",$Q198="N/A"),"N/A",NETWORKDAYS($N198,$O198,(IF($Q198="PH",'PH Holidays'!$B:$B,IF($Q198="IN",'IN Holidays'!$B:$B,IF($Q198="US",'US Holidays'!$B:$B,"0")))))),0) | =IFERROR(K198/R198,"N/A") | =IF(VLOOKUP(U$2,'CFS Testing Hrs working file'!$A:$B,2,FALSE)=1,0,IF(VLOOKUP(U$2,'CFS Testing Hrs working file'!$A:$B,2,FALSE)=7,0,IF($Q198="us",IF(IFERROR(VLOOKUP(U$2,'US Holidays'!$B:$B,1,FALSE),0)=U$2,0,IF(U$2>=$N198,IF(U$2<=$O198,$S198,0),0)),IF($Q198="PH",IF(IFERROR(VLOOKUP(U$2,'PH Holidays'!$B:$B,1,FALSE),0)=U$2,0,IF(U$2>=$N198,IF(U$2<=$O198,$S198,0),0)),IF($Q198="in",IF(IFERROR(VLOOKUP(U$2,'IN Holidays'!$B:$B,1,FALSE),0)=U$2,0,IF(U$2>=$N198,IF(U$2<=$O198,$S198,0),0)),0))))) | =IF(VLOOKUP(V$2,'CFS Testing Hrs working file'!$A:$B,2,FALSE)=1,0,IF(VLOOKUP(V$2,'CFS Testing Hrs working file'!$A:$B,2,FALSE)=7,0,IF($Q198="us",IF(IFERROR(VLOOKUP(V$2,'US Holidays'!$B:$B,1,FALSE),0)=V$2,0,IF(V$2>=$N198,IF(V$2<=$O198,$S198,0),0)),IF($Q198="PH",IF(IFERROR(VLOOKUP(V$2,'PH Holidays'!$B:$B,1,FALSE),0)=V$2,0,IF(V$2>=$N198,IF(V$2<=$O198,$S198,0),0)),IF($Q198="in",IF(IFERROR(VLOOKUP(V$2,'IN Holidays'!$B:$B,1,FALSE),0)=V$2,0,IF(V$2>=$N198,IF(V$2<=$O198,$S198,0),0)),0))))) |

<tbody>

</tbody>

As you can see there are some references to some other sheets in the book where i keep track of the # of holidays etc. Not sure if the network days forumla is throwing me off or if its the nested if with the vlookups for each of the dates (remember there are 365 per line item and there are 30K line items). Any ideas on how to simplify this down to one book would be greatly appreciated.

If you have any questions due to my poor explanations above please feel free to ask.

Thanks in advance.