Return Multipe Cells

Brandon Belz

Hi Smartsheet community,

 

I have the following columns:

[Name] (text/number value)

[Team] (drop down value)

[Budget Milestone] (checkbox)

[Program Milestone] (checkbox)

[Project Milestone] (checkbox)

Now I am unable to run a report because the what in report builder condition is either all 'OR' or all 'AND', not all tasks assigned to a team will all be all three milestones, and if or, it will return all milestones assigned to all teams. I need Budget Milestone or Program Milestone Or Project Milestone and Team X. Currently, I would have to run (3) separate reports.

 

Even if report did allow "Or" for the 3 checkbox fields with "And" X Team, I now think that it would not return a value if more than one were checked, tbd when that feature is added.

I am trying to do something similar to as seen here:

https://www.ablebits.com/office-addins-blog/2017/02/22/vlookup-multiple-values-excel/

I have also come across some posts regarding array formulas; are they still not supported in your software?

 

Best,

Brandon

Andrée Starå

Hi Brandon,

If you can put the logic for the report's criteria in the sheet(s), you could probably make it work.

Example. Checkbox when criteria are true, and then the report would be configured to show that. 

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

Can you send us a screenshot of you're sheet and highlight the rows you'd expect to see in the report? I think we might be able to come up with a method for gathering the data you want. Seeing the sheet would help a ton. 

Mike Wilday

Or Andree's suggestion is also a possiblity. Do the login in a "helper" column that will indicate the rows you'd want in the sheet. And you would fill the item based on that. 

Brandon Belz

The columns are listed above and their types too and I spoke to a Smartsheet rep' and they are building out the report capabilities. Right now you can only have all ands or all ors.

 

All ands would leave out things just one of milestones, and all ors will not return just the one teams I need.

I am trying to figure out if there is a formula similar to array in excel that can return all milestones.

Does anyone know when array will be available?

-B

Brandon Belz

Hi all,

 

The columns are listed above with their types to but here is a diluted screenshot example of my sandbox.

The report cannot be tailored as its either all AND or all OR, therefore returning everything and anything assigned to the team or one of the 3 boxes checked. Other side of the coin is returning all AND which is such a narrow return leaving out anything that does not have all 3 checked.

Does anyone know when array will be used in excel?

I am want to be able to aggregate everything assigned to one teach if any or all of the 3 possible boxes are checked. I am stuck somewhere between not having array, IFERROR + IF + VLOOKUP.

Here is a formula I have got working that does return the value within the name column but I don't need a helper column that will be just as long as the source sheet. I need the results returned if and only one or all boxes are checked.

=IFERROR(IF(AND($[Responsible IT Track]@row = "Video/Voice", [Budget Milestone]@row = 1), $[Task Name]@row), "")

-B

Brandon Belz

The columns are listed above with their types too. Attached is a snipit of a diluted version I am chewing on in my sandbox.

Generating a report cannot work until Smartsheet builds out the reports capabilities mid 2019.

A helper column would be the same length as the source sheet and there are ~8 teams so I would need 8 helper columns. I want to return all results if and only assigned to "Video/Voice" if one or all three boxes are checked. However, the more I think of it the helper column I think may be the best bet, if I can hide the column and still pull data from it that may be a work around right now

-B

Andrée Starå

Great!

Let us know if you have any other questions or if you need any help.

Best,

Andrée

Mike Wilday

Yes. You can hide the helper column! And set an if statement that would give you a flag or a status if the content matches your exact logic. You will have to add the helper column to your report initially but then after it's developed you can hide it.