License required

jb@59069

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?

Paul Newcome

Is it just one sheet that you are reporting on or multiple?

Paul Newcome

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.

Andrée Starå

Hi Jerry,

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!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

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?

Paul Newcome

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.

Andrée Starå

Nicely done!

Every day we learn something new! 

Paul Newcome

Thanks! 

 

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?

Andrée Starå

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?