Formula error

Leighmercado

what is wrong with my formula? I've tried different variation but always getting #Unparseable error

=IF(P2F_status_1Q2024 Range 1 = "Promo", P2F_status_1Q2024 Range 4)

=IF('P2F_status_1Q2024 Range 1' = "Promo", 'P2F_status_1Q2024', Range 4)

=IF('P2F_status_1Q2024 Range 1' = "Promo", 'P2F_status_1Q2024' Range 4," ")

What i need is if range 1 is marked as "Promo" the cell will return the value in Range 4

Kelly Moore

Hey @Leighmercado

Before addressing the possible syntax error in the range identification, a point of clarification please. Are both your Range 1 and Range 2 each pointing to single cells, or do the ranges refer to a group of cells, for instance an entire column?

I'm also assuming your ranges refer to a different sheet?

Kelly

Leighmercado

@Kelly Moore the range is an entire column

Kelly Moore

Hey @Leighmercado

Thanks for the clarification. Since the range is more than a single cell, the equation above says when column A = "Promo", Column B. Smartsheet cannot make that type of evaluation on an entire range. The formula above cannot work the way it is written above.

Are you trying to bring data from one sheet into another? If you could provide some screenshots, I will be happy to help you construct the formula that will help you obtain your desired results.

Leighmercado

@Kelly Moore I am trying to create an intake form for my dashboard that's connected to my main log.

column "cust" is my range 4 and column "exceptions" is my range 1 (i filtered to show my promo for the purpose of Q&A. :) )

Kelly Moore

Hey @Leighmercado

What info do you wish to share on the dashboard? If you are wanting to share multiple rows at a time, like the data you show in your screenshot, one option is to create a report and bring the report to the dashboard. We could filter it to restrict to only the data you want show. The data would always be current, based on the criteria that is set up in the report.

Would this work for you?

Kelly

Leighmercado

@Kelly Moore , thats one of my issue - i cant seem to connect a report to a dashboard - i have to create a separate sheet so i can connect. THis is the report that I created that i cannot connect to.

Kelly Moore

@Leighmercado

What widget are you using to connect to the dashboard? What permission level do you have on the report? Are you trying to connect a published report?

If using a regular (non published version) of the report, you should be able to connect using the Report widget.

Leighmercado

@Kelly Moore

Was trying to add chart and metric widget and it snot a published report (but it is shared - not sure if its the same)

Kelly Moore

@Leighmercado

Sorry for the delay. You mention you are trying to use a metric widget. What data point are you trying to bring in with the metric widget?

Is the data in the report summarized (counts, sums, etc) in your report? If yes, you should be able to chart this.

Kelly

Leighmercado

@Kelly Moore Thank you for your help!

THe data in the report is not summarized. Normally i would do a pivot table in excel but I dont think its a feature in smartsheet. SO im trying to do a report that will give me the total that will be automatically update in my dashboard

Kelly Moore

Hey @Leighmercado

Smartsheet does have a Pivot App option. You will need to check to see if it is available in your license plan. Smartsheet current documentation calls out Smartsheet Advance Package license. I believe this is a restructuring of some other plans.

As an alternative, one can often pull the same data using smartsheet standard reports and utilizing the Groups and Summarize options. These options are found in the Report builder parameter section.

You can find information here: https://help.smartsheet.com/learning-track/best-practice-webinars/reports

or here: https://help.smartsheet.com/articles/2482082-configure-grouping-to-organize-results-in-report-builder

If you share your current report settings, I may be able to help you get closer to what you need. Play with the Groups and Summarizing parameters to see if you can get your data

Kelly

Leighmercado

@Kelly Moore I will look into the links! Thank you soooo much!!!