Does Smartsheet have a Debug tool?

MPMurphy

Having trouble getting the combination of INDEX(COLLECT)) functions to return the correct data. Is there a "Debug" tool similar to that available in Visual Basic that allows you to step through the formula?

Thanks in advance.

Paul Newcome

There is this area here that shows where you are within the function and gives a little bit of info on what goes in that piece.

You can also reference this for a list of all functions and some help tips/etc. on using them:

https://help.smartsheet.com/functions

And here is a reference for errors, their potential causes, and some troubleshooting tips:

https://help.smartsheet.com/articles/2476176-formula-error-messages

Paul Newcome

If you want to post more details about the formula, source data, and intended result (screenshots are helpful too), I'd be happy to see if I can help work through it with you.

MPMurphy

@Paul Newcome thanks for your response. I have used all of the references you have above to no avail.....yet. :) I think I am not completely understanding how the @cell and @row functions work. My formula is executing (was able to get rid of the NOT PARSABLE error followed by the INVALID REFERENCE error), but I'm not getting the data I want. I will need to do some more work in the sandbox. I was hoping there might have been a debug tool that shows you the value of the formula as you step through.

MPMurphy

Sounds good. I will try to do that over the weekend, but it might have to wait until Monday. :) I appreciate the offer!! Thanks.

Paul Newcome

I'll keep an eye out in case you do end up posting the formula here.

A little info on @row and @cell...

@row: This is simply telling the formula to look at the column name on whatever the row the formula is on. this way you do not have to enter a specific row number, it just automatically looks across the same row.

[Column Name]@row

@cell: This basically tells the function to evaluate the previously established range on a cell by cell basis instead of the entire range as a single entity. For example: Lets say you want to count how many dates fall within the year 2022.

If you use

=COUNTIFS([Date Range}, YEAR({Date Range} = 2022)

then you are probably going to get a result of zero because chances are the entire range as a single entity does not equal 2022.

But you need SOME kind of reference for the year function. That's where "@cell" comes in. We tell the COUNTIFS to count through this range on a cell by cell basis how many of those have the year of 2022.

=COUNTIFS({Date Range}, YEAR(@cell) = 2022)

MPMurphy

@Paul Newcome

I have two sheets I am using. The first sheet is called "ELZ Controls Resource Planning". This is basically a task list or a task/project schedule I am using to track resource loading of three technicians. The second sheet is called "Outlook Calendar 2022-2023". My company divides the year into thirteen four-week periods. This sheet is used as a "lookup table" when using formulas on the "ELZ Controls Resource Planning" sheet.

When the "Start Date" and "End Date" are entered for a (any) task in "ELZ Controls Resource Planning", I want the INDEX(COLLECT)) nested functions (in "ELZ Controls Resource Planning") to search the "Week Start Date" and "Week End Date" columns in "Outlook Calendar 2022-2023" for the corresponding period and week in the "Subject" column (in "Outlook Calendar 2022-2023") and return the corresponding value.

I believe the formula to look up the "Start Date" (in "ELZ Controls Resource Planning") should resemble the following: INDEX(COLLECT({Outlook Calendar 2022 Range 1}, {Outlook Calendar 2022 Range 2}, @cell <= [End Date]@row, {Outlook Calendar 2022 Range 6}, @cell >= [Start Date]@row), 1))

where:

({Outlook Calendar 2022 Range 1} = the "Subject" column

{Outlook Calendar 2022 Range 2} = criterion range 1

@cell <= [End Date]@row = = criterion 1

{Outlook Calendar 2022 Range 6} = criterion range 2

@cell >= [Start Date]@row) = criterion 2

, 1)) = last argument of INDEX function

In plain English, I want to take the "Start Date" in "ELZ Controls Resource Planning", compare it to the range that begins with "Week Start Date" and ends with "Week End Date" in "Outlook Calendar 2022-2023", and if it is between those two dates, return the contents of the "Subject" cell to the corresponding "Period Start" cell in the "ELZ Controls Resource Planning" sheet. Do the same thing for the "End Date" and corresponding "Period End".

I am close to getting the right data, but as you can see from the entries, I'm not returning the correct period and week.

For example, in row #1, a "Start Date" of 1/10/22, is returning the value "P1 W2 - week 2", which is correct because in sheet "Outlook Calendar 2022-2023", "P1 W2 - week 2" begins on 1/9/22 and ends on 1/15/22.

However, the end date does not come back correctly. I think there is an issue of how the "<=" and ">=" are being evaluated between the "@cell" and "@row" functions.

Also, when the duration is just "1 day", the error "INVALID VALUE" is shown. So there is something going on there as well.

ELZ Controls Resource Planning.png

Outlook Calendar 2022-2023.png

Period Finish Formula.png

Period Start Formula.png

Paul Newcome

We can actually get away with only referencing a single date column for each.

We pull the largest date from the table's start column that is less than or equal to the start date. That will give us the start date for the week it is in:

=MAX(COLLECT({Calendar Sheet Week Start Column}, {Calendar Sheet Week Start Column}, @cell<= [Start Date]@row))

Now we can match on this in a basic INDEX/MATCH to pull the start period:

=INDEX({Calendar Sheet Subject Column}, MATCH(MAX(COLLECT({Calendar Sheet Week Start Column}, {Calendar Sheet Week Start Column}, @cell<= [Start Date]@row)), {Calendar Sheet Week Start Column}, 0))

Finally we use the reverse logic to pull the week for the end date. Lowest date that is greater than or equal to the end date:

=INDEX({Calendar Sheet Subject Column}, MATCH(MIN(COLLECT({Calendar Sheet Week End Column}, {Calendar Sheet Week End Column}, @cell<= [Start Date]@row)), {Calendar Sheet Week End Column}, 0))

MPMurphy

I guess I was making this a little more difficult than needed. I'll give this a shot later this week. Thanks for all the help!!

Paul Newcome

Happy to help. 👍️

Feel free to revisit if you have any issues.

MPMurphy

Sorry for the delayed response - got it working! Thanks for the assist. 😁