Project scheduling - moving from Excel

ajsmith

We currently use Excel but want to move our Scheduling spreadsheet to Smartsheet.

Here are some details:

• We have 8 employees that can weld.
• Some of the 8 can work on small parts, some can work on large parts, and some can work both.
• Our Sales Orders may contain small parts, large parts or both. We list how much time for each and use in formulas to determine if we are over-scheduling based on ship date.
• We plan weeks and months ahead.
  
  
  
  We don't assign welders to orders, but tally up how many hours are available vs what is due that week. For example:
  
  
  
  -we have 14 welders hours available for small parts and need 10 hours. Therefore, we have an extra 4 hours available.
  
  -we have 42 welders hours available for large parts, but need 50 hours. Therefore, we are short 8 hours.

We currently have formulas that total the hours up and compare them. Using conditional formatting, if the hours available are lower than the hours needed, the cell will turn red. (see screen shot) 

I'm trying to figure out a way to import this into Smartsheet. Any ideas on the best way to do this? Right now we are doing a lot of manual entry.

Paul Newcome

Which parts exactly are you looking to automate? It sounds like the majority of it can be, but it would be helpful to know what you already have in place.

ajsmith

Right now our Shop Manager has to manually add new welders and change formulas to account for their daily availability.

He also manually enters the average number of hours available each day. While there is an AVERAGEIFS in Excel, it is not available in Smartsheet. 

Paul Newcome

Can you post the formulas that need to be changed? You should be able to automate most of this process.

 

While there is no AVERGEIFS there is an AVERAGEIF that allows for a single set of criteria. If you need more, an AVERAGE(COLLECT( could be written out.

ajsmith

I need to find the average for the column Total Acc Hours Avail for dates between today and 2 days from now (7-24, 7-25 and 7-26). 

I was trying this:

=SUMIF({Daily Welder Hours Range 2}, <=Dates1, {Daily Welder Hours Range 3})

But can't figure out the between dates part. 

Paul Newcome

Try something like this...

 

=AVERAGEIF({Daily Welder Hours Range 2}, AND(@cell >= TODAY(), @cell <= TODAY(2)), {Daily Welder Hours Range 3})