I am having multiple users begin receiving this message when accessing reports that they have been using for many, many months.
I have reached out to Smartsheet Support yesterday and today with no response.
Can anyone here help?
Is it just one sheet that you are reporting on or multiple?
I have a solution, but I will have to get back to you tomorrow. I am out of time for today (gotta love meetings...). My apologies.
I think that a workaround could be to have a top row in the report were they input their criteria and then use that to check a box in a helper column and then show those in the report.
Another method could be to use a form to input the criteria and then that would update what to show in the report.
What do you think? Would any of those options work?
Hope that helps!
Have a fantastic weekend!
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
It is only somewhat tedious for 1 sheet, but obviously additional sheets create even more tedium. BUT it will at least get you back to the contractors being able to select their own dates. It may not be as ideal as your previous setup, but unfortunately we're just going to have to work with what we've got. So here goes...
Create a 3rd sheet for each contractor. This 3rd sheet will have 5 columns and a form.
[Created (date)] [Billing Period Start] [Billing Period End] [Start Date] [End Date]
The top row of this sheet will leave those first three columns blank. In [Start Date]1, enter the following
=INDEX([Billing Period Start]:[Billing Period Start], MATCH(MAX([Created (date)]:[Created (date)]), [Created (date)]:[Created (date)], 0))
This will pull the most recently entered date from the [Billing Period Start] column.
In [End Date]1, repeat the formula except point it towards the [Billing Period End] column.
The contractors would fill out a form that simply has access to the start and end dates. Set the form so that it takes you to a specific URL when an entry is submitted, and set that URL as the one for that particular report.
Next you would go to the sheets containing the source data.
Add 3 columns.
[Period Start] [Period End] [Needed for Report]
The start and end would be date type columns and the 3rd would be a checkbox.
Use cell linking to bring the Start and End dates from the form sheet over.
Then in the checkbox column, use something along the lines of
=IF(AND([Date Column]@row >= [Period Start]$1, [Date Column]@row <= [Period End]$1), 1)
This will check the box for any rows that have a date between the start and end period submitted through the form by the contractor.
This portion will happen automatically as soon as a form is submitted. That's why we are able to have the form redirect to the Report's URL.
All of the calculations will happen as soon as the contractor submits the form, and they will automatically be redirected to the updated report.
I know it seems like a lot of work, but each step is relatively straightforward and will build out something that might work.
Does it sound like something you could use?
I was in the middle of typing up a form based solution when I had to leave to go pick up my little one. I just finished it up and posted it below.
I do like using the top row in the report as well though. I hadn't thought of doing it that way.
Every day we learn something new!
And very true.
My thoughts on a row in the report would be something along the lines of having a dedicated row on every sheet and some sort of helper column that looks at that row to determine what goes into the report?
That seems like it would be an easier setup and use since the contractors could enter it right there on the report. Only one link for them to use instead of one for the report and one for the form.
And cell linking could be used if the report were looking at multiple sheets. The only time I could see it getting a little complicated is if you had multiple reports looking at multiple sheets with overlap.
Report 1 is looking at sheets A and B, and Report 2 is looking at sheets B and C. Sheet B would need 2 dedicated rows and 2 helper columns.
Or did you have some other setup in mind?
Reading your more detailed description, I can see that it would be more work than I thought initially.
You won't need to create new sheets but new section/row(s) in the different sheets.
How many records can there be in the individual reports?
I've been trying to do a simple check of whether or not the entry from the form has a returned value that is either yes or no and it checks 3 cell locations to see if any returned yes. The 3 columns are Dropdown lists for Yes/No and the Status is a Text/Number, is that the issues? If not how do i fix that?
I'm wondering if there is a way to lock rows 1-12 out of the sorting and filtering of a sheet. I might have take out that part of the from otherwise I dont like how the built in dashboard moves when a sort or filter is applied?
Hello, I apologize if this has already been answered, I did search before asking. I recently downloaded a template and it had the google slides displayed in presentation mode. I cannot figure out how to do this as when I embed, it's just the entire google page where you can edit and see the menu bar. How do I embed the…
Help shape the future of Smartsheet.
Share your ideas and feature requests.
©2023. All Rights Reserved Smartsheet Inc.