Excel Running out of resources - maybe you can help?

Labamba32

New Member
Joined
Oct 8, 2013
Messages
21
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):

Blank ColumnBlank ColumnTask NameTask #ReferenceProject ReferenceLocation# of ItemsTime FrameClassificationHours to CompleteTask TypeEmployee AssignedStart DateEnd Date# of DaysLocation of AssignedWorking DaysHours/DayBlank Column1/1/191/2/19
Task A1SalesProcess ImprovementUSA40Q1X13TestMark1/1/191/5/195USA5111
Harded Coded DataHarded Coded DataHarded Coded DataHarded Coded DataHarded Coded DataHarded Coded DataHarded Coded DataHarded Coded DataHarded Coded DataHarded Coded DataHarded Coded DataHarded Coded DataHarded 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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,295
Messages
5,595,289
Members
413,984
Latest member
stikpet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top