Excel Running out of resources - maybe you can help?

[Labamba32]

Maybe my sheet is inefficient but what i am trying to do is have a task list scheduling tool. Basically I have a list of 30K tasks that can be assigned to various team members. Each task has a certain amount of time allowed and a start and end date. If a task takes 5 hours and we have the start date set for 1/1 and the end date for 1/5 we straight line the time for 1 hour per day. Then we sum the hours assigned per day to ensure that we have no overloaded any individual employee.

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):

[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD]Blank Column[/TD]
[TD]Blank Column[/TD]
[TD]Task Name[/TD]
[TD]Task #[/TD]
[TD]Reference[/TD]
[TD]Project Reference[/TD]
[TD]Location[/TD]
[TD]# of Items[/TD]
[TD]Time Frame[/TD]
[TD]Classification[/TD]
[TD]Hours to Complete[/TD]
[TD]Task Type[/TD]
[TD]Employee Assigned[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]# of Days[/TD]
[TD]Location of Assigned[/TD]
[TD]Working Days[/TD]
[TD]Hours/Day[/TD]
[TD]Blank Column[/TD]
[TD]1/1/19[/TD]
[TD]1/2/19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Task A[/TD]
[TD]1[/TD]
[TD]Sales[/TD]
[TD]Process Improvement[/TD]
[TD]USA[/TD]
[TD]40[/TD]
[TD]Q1[/TD]
[TD]X1[/TD]
[TD]3[/TD]
[TD]Test[/TD]
[TD]Mark[/TD]
[TD]1/1/19[/TD]
[TD]1/5/19[/TD]
[TD]5[/TD]
[TD]USA[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]Harded Coded Data[/TD]
[TD]=O198-N198+1[/TD]
[TD]=IFERROR(VLOOKUP($M:$M,'Resource Roster'!$B:$E,4,0),"N/A")[/TD]
[TD]=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)[/TD]
[TD]=IFERROR(K198/R198,"N/A")[/TD]
[TD][/TD]
[TD]=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)))))[/TD]
[TD]=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)))))[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[bobsan42]

I can't imagine you need to or can see full year planning at once. I can't even imagine you can plan 30k tasks at once.
My approach would be to keep all permanent (hard coded) data on one sheet. Then on another sheet show only the tasks and planning for a selected period week, month, date range.

Or use ms project.