Best Of
Re: How to Auto Delete Duplicate Entries based on a User ID and Date?
The way I do this in my sheet is to have a helper flag column, just like you have! Then I use a formula to count how many rows that match my conditions appear in the current sheet. If there are more than 1 that match, I flag those rows. This way, each duplicate is flagged and I can manually delete one of the two.
For example:
=IF(COUNTIFS([Content for the Week of]:[Content for the Week of], [Content for the Week of]@row, [E Number]:[E Number], [E Number]@row) > 1, 1, 0)
You can then also set up Conditional Formatting so those rows are highlighted in a certain colour to make them stand out.
Let me know if that helps!
Cheers,
Genevieve
Genevieve P.
Re: Smartsheet API in Python: Inaccessible 429 Response
In case anyone finds this thread, I posted this to stackoverflow and received this answer:
Apparently in the OperationErrorResult class, there are a few errors which undergo an auto-retry:
4003: {
'name': 'RateLimitExceededError',
'recommendation': ('Retry using exponential backoff. Hint: '
'Reduce the rate at which you are sending '
'requests.'),
'should_retry': True},
So the error is handled and the request is automatically retried.
If anyone wishes for further details, please see the linked post to stackoverflow
I_B
Re: =(INDEX(COLLECT Invalid Value Error
The text in the formula column on your target sheet is "Zit Zap" whereas the text in the formula column on the source sheet is "ZitZap" you have a space between one and not the other. This means the COLLECT function is pulling in zero rows, so the 1 in the INDEX function indicating to pull from the first row is causing the error (since there is no "row 1" for the INDEX function to pull from).
Paul Newcome
Re: Top 5 Results
My suggestion would be a hidden helper column (checkbox) with the following formula:
=IF([Number Column]@row>= LARGE([Number Column]:[Number Column], 5), 1)
This will check the box for the top 5. You can then filter your report based on this box being checked.
Paul Newcome
Re: Count tasks due in the next 14 days with multiple status from another sheet
Hi @k.platt
This can be solved with a simple trick - counts two statuses separately and add them to get her:
=COUNTIFS({121 Task Status}, "Not Started", {121 Task Hierarchy}, "Child", {121 Finish Date}, <=TODAY(+14)) + COUNTIFS( {121 Task Status}, "In Progress", {121 Task Hierarchy}, "Child", {121 Finish Date}, <=TODAY(+14))
Let me know if this works?
Good Luck!
Tomasz Giba
Re: Auto Delete rows
Hi,
@Genevieve P. , I found a neat way to auto-delete through data shuttle!!!!!!!!!!!!!!!
We have an archive sheet that has a heavy volume of stuff that keeps accumulating that we don't need -
I set up an data shuttle where the source is an empty excel attachment and targets this archive sheet and is set to "Replace all target sheet rows with the data from the input file"
I set this to run every day and it really deletes all the rows daily!!!
@Jake Gustafson @Genevieve P.- in your case you can set up another real_archive sheet and when the row number on your first archive sheet reaches a certain limit you can auto-move them to the real_archive sheet which has the data shuttle set up and auto-deletes!
@Automations 1 , I started using this for some other archive sheets
ygoldgrab
Re: How do I create a workflow to record a date in the future?
Greetings,
If you are trying to create a date 1 year in the future based on an approval date, I'm guessing you already have a column on your sheet for review date, correct?
If so, I would use a formula to calculate the review date in the column and not need a workflow. The formula would calculate the date 1 year in the future based on the request approved date. Using the column formula option would populate the formula to the entire column.
An example: =IFERROR(WORKDAY([Request Approved]@row, # of days in the future, holiday list if available), " ")
The formula will create a date in the future based on working days (excludes Saturday and Sunday). If you have a list of other holidays you can use that list in the formula.
Based on a start date of today there are aprox. 250 working days until next year. Your days may vary but wanted to provide a starting point.
I hope this helps.
Frank S.
Re: =INDEX MATCH .. formula
It sounds like an INDEX(MATCH will work for you, as long as the address is exactly the same in both sheets!
An INDEX(MATCH works like this:
=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))
You'll notice that the Match requires two values separated by a comma, instead of how you have a ) between the Address and {range}.
Your syntax is just a little off, try this:
=INDEX({sheet names Range 1}, MATCH(Address@row, {sheet names Range 4}, 0))
Let me know if this works for you!
Cheers,
Genevieve
Genevieve P.
Re: Automate data flow from Source to Target sheet (based on New & Edit criteria)
Hi @PKotha
I hope you're well and safe!
To add to Amit's excellent advice/answer.
Another option (depending on your structure and specifics) would be to only use cross-sheet formulas (VLOOKUP or INDEX/MATCH), so there is no need to copy the rows.
You'd use an auto number column in the source sheet and then add a so-called helper column in the destination sheet where you'd add the number manually, and we use that to connect the rows.
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
Andrée Starå
Re: Automate data flow from Source to Target sheet (based on New & Edit criteria)
HI @PKotha
I should able to config what columns need to move target sheet.
Unfortunately, when you apply copy/move automation, all the columns are moved to the destination. Here is a workaround that I would propose.
- Create an automation which copies all the new rows (including all the columns) into the destination sheet.
- All the columns in the destination sheet, except the primary key should be hidden.
- Create new columns with same name as the hidden columns (with some variation as Smartsheet does not allow duplicate column names). You can limit the new columns to only those which you desire to be shown on the second sheet.
- In these columns, apply INDEX/MATCH formula to reference data from the source sheet based on the primary key.
I hope this helps.
Best Regards
Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA
Amit Wadhwani
