Index/Collect

Hi. This is my first time posing a question on the Smartsheet Community. I will try to describe what I am looking for as I am trying to write a formula and not having much success. Your assistance is greatly appreciated.

Background:

I have a company that makes widgets for a multitude of projects (50+) at one time. Each widget takes a certain amount of time to build, and this time varies depending on complexity for example. The time it is projected to take to build a particular widget is contained on a column called "Budgeted Shop Hours" and the sheet where this information is stored is on each individual project's Job Log. The Job Log is also where the below columns originate.

The total shop hours available per week is variable depending on who shows up for work, for instance. This information is held on a sheet called Current Capacity which needs to be cross-referenced. The number of men times the hours they work is held in a column called "Weekly Hours Available." This sheet consists of up to 5 weeks of time for each month (depending on the number of days in a month, the 5th week may or may not be used. At the end of the month, it starts over. Here is a screenshot of this sheet:

Screenshot 2024-06-22 at 10.31.05 AM.png

The "Hours Remaining" is the difference between the "Weekly Hours Available" and the "Budgeted Shop Hours" of each widget. We compile this info by creating a report of all widgets that are ready for production during any particular week in order to aid in production scheduling . We call this report Demand Estimate.

Screenshot 2024-06-22 at 10.49.00 AM.png

Is it possible to write a formula (perhaps INDEX/COLLECT?) that gets the total number of hours available for each week (ie, week 1, week 2…), and then subtracts it from the time it takes to make whichever particular widget is scheduled to be produced that week as reflected on the report. Causing me even more difficulty, the "Hours Remaining" for the first item produced each week will start with the total hours available with overtime, but the next item will be dependent on how long it takes to make the first widget that is scheduled for that week so the formula will need to take into account the "Hours Remaining" from the previous widget on the report. I hope I have explained this clearly enough for help.

Does anyone have any thoughts on how to approach this or if it is even possible?

Find more posts tagged with

Accepted answers

KPH

Too many squiggles. In cross sheet references you only need one curly brace around the references (you use two in field names for automations). Try this:

=INDEX({Current Capacity with OT}, MATCH([Week]@row, {Current Capacity Week}, 0))

🤞

All comments

Chris Stutz

Does anyone have an opinion if this is even possible?

KPH

Hi Chris,

Yes, if I have understood the requirement, this is possible. I think there are two things blocking you.

You are using a report to do the calculations but a report cannot have formulas in it. You would need to do the math in a sheet and then generate a report.
You aren't able to reference the numbers that you need to do the math.

So addressing the second issue first…

You need to be able to identify where in the Current Capacity sheet, the data that you need to reference will appear.

You can do this with cell links but it would be ridiculously time consuming.
You could do this based on the column that has "Week x Totals" in it but that would mean editing the formula for each week.
So, while not strictly necessary for what you want to achieve, my preference would be to reformat the Current Capacity sheet and make it pure data, without headings and totals (you can create a report to get the totals).

I don't have a lot of time right now but wanted to respond to you, as I can see it has been a few days. So, I haven't set this up in smartsheets but have drawn it quickly. Despite what it may look like, I am not a serial killer! How would you feel about deleting all the rows I have redlined and adding another column with week numbers? Week Number could remain as a row if you're happy to make each of the Locations a child row below the week number.

This will let you do things like SUM the values in a column IF the weeknumber matches a particular weeknumber.

If you can't edit the sheet, the alternative is to use INDEX and MATCH as you were heading towards. You would INDEX the total column and MATCH on "Week 1 Totals" for the first week, "Week 2 Totals" for the second etc.

Whichever way you go, I think you need another sheet to do the math in.

Hopefully this is helpful. If you need help with specific formula reach out.

Good luck!

Chris Stutz

This is very helpful, KPH!

Thank you so much for the time you've taken to point me in the correct direction. I didn't have a chance to work on it yesterday, but I'm going to take your advice and see what I can do from there. I will reach back out with specific questions as they arise.

Chris Stutz

I've reformatted the Current Capacity as you suggested above and made another sheet called Calcs (screenshot below) to do the math.

Screenshot 2024-06-26 at 11.29.23 AM.png

Trying to work on the formula to retrieve the total hours available each week from the Current Capacity sheet.

I tried the following formula and am getting UNPARSEABLE.

=INDEX({{Current Capacity with OT}}, MATCH([Week]@row, {{Current Capacity Week}}, 0))

Thoughts?

KPH

Too many squiggles. In cross sheet references you only need one curly brace around the references (you use two in field names for automations). Try this:

=INDEX({Current Capacity with OT}, MATCH([Week]@row, {Current Capacity Week}, 0))

🤞

Chris Stutz

Thank you! This is awesome!

This is the first part of a larger project so I am sure I will be back again. :-)

KPH

I had a feeling you were on the brink of a larger project. Hopefully organizing the sheet in this way will help all future endeavors. Good luck!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.