Can I request an input variable

I have a lot of dashboards showing charts etc. from summary fields, reports and direct from the smartsheet. I calculate a lot of summary fields based on dates - for example yesterday, this week, last week, this month, last month and tend to use helper fields of true & false with formula to calculate as this then simplifies the summary feld formulas. All this is working brilliantly and I have had community help to get to this utopian stage.

However………

I have a set of records input each day / week with our financial week number and I would like the opportunity to create a set of figures based on a week number that I input. For example [Last Week] = "True" for us is week 42 as we are currently in week 43.

I'd like the option to input somewhere????? a variable [Week Number] for example 40 where it would give me the Stoke Items (W) for the data where the week number on the records = 40 rather than a fixed last week, this week etc.

I think I used to be able to add a user input field in Excel but I so rarely use excel these days I cnnot even remember how I did that 😫

Really hpe someone can help me as it would be awesome.

Many thanks in advance for your patience and help

Sue

Find more posts tagged with

Accepted answers

SueinSpain

So for anyone following this thread……. I have a solution / workaround

(1) I have created a smartsheet with one record that the users can update to select the fiscal week they want reported up.

(2) Next I have a a smartsheet summary field in my main smartsheet for the optional week with a formula to pick up that week from the smartsheet in step (1) above

(3) I then have a helper field in my smartsheet set to True or False on whether the record falls into this optional week

Now I can perform any calculations or reporting on the optional week = True as it keeps all the other formula more streamlined without too many nested IFs

I've also learnt how to determine what is the most recent week as that can depend on when the users are looking at the data and when they've put the figures in. So a nice little formula to set the Latest Week to be true or false as I hadn't come across the MAX function before

All comments

Nathan Slatton

Id say use a nested if statement in there for your variable if([column name]cell number < 40, true,false)

SueinSpain

So… I can set up a summary field and use that in the formula to know whether a slected week or date no problem. Change the summary field and the formula updates whether True or False etc.

However, I would like that Sheet summary field to be input on my dashboard rather than have a licenced user open the smartsheet and update this field. Is this possible? Or easier than….

Having a form on the dashboard for the user to imput the week number or date to a helper smartsheet and use that to index whether true or false?

I can think of workarounds but just wondered if there was a more simple way to achieve this?

SueinSpain

So for anyone following this thread……. I have a solution / workaround

(1) I have created a smartsheet with one record that the users can update to select the fiscal week they want reported up.

(2) Next I have a a smartsheet summary field in my main smartsheet for the optional week with a formula to pick up that week from the smartsheet in step (1) above

(3) I then have a helper field in my smartsheet set to True or False on whether the record falls into this optional week

Now I can perform any calculations or reporting on the optional week = True as it keeps all the other formula more streamlined without too many nested IFs

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.