Best Of
Re: Question Using TODAY Formula
I hope you're well and safe!
Please have a look at my post below with a method I developed to update the sheet(s) daily.
More info:
Would that work/help?
I hope that helps!
Have a fantastic week & Happy New Year!
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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
Re: Question Using TODAY Formula
A good workaround I found is to create a hidden helper Date column called "CurrentDate", then create a "Record a Date" automation rule that everyday places the current date in that column for every populated row in the sheet (use the condition block with something like Where Primary Column is not blank.) Then your formulas can use CurrentDate@row instead of TODAY() and won't need the sheet opened in order to refresh the current date.
Edit: Just realized Andree pointed you to the same workaround :) Good luck!
Re: Linking directly to files from a dashboard?
Hi everyone! To those who were able to try Dustin's workaround to copy the download link, did you experience link expiration after 30 days? It says, active for 30 days
Re: If AND or OR formula
It depends on what exactly your priorities are. The formula will read from left to right and stop at the first true value. If you want yellows to take priority over not needed, then the yellow IF should come before the not needed IF.
My suggestion would be to write out how you want the formula to function keeping in mind priorities and how nested IFs work (if it makes it to the 3rd IF then the first two must be false).
If this column is blank --> Not Needed
If all columns are Green --> Green
If any are Red --> Red
If any are Yellow --> Yellow
If the first column is not blank and there are no reds, yellows, or greens (meaning all is false) --> Not Needed
Put your IF statements in this order as well
IF([Column Name]@row = "", "Not Needed", <<>>)
IF(AND(all_columns_green), "Green", <<>>)
IF(OR(any_columns_red), "Red", <<>>)
IF(OR(any_columns_yellow), "Yellow", <<>>)
"Not Needed"
Of course you may need to adjust the orders to fit your preferred priority, but this exercise will help you get everything laid out the way you need it to be.
Now work from the bottom up. Put the "Not Needed" in place of the <<>> in the 4th IF. Take what you now have and put it in place of the <<>> in the 3rd IF. Take that new string and drop it in place of the <<>> in the 2nd IF. Take all of this now and drop it in place of the <<>> in the 1st IF.
Throw an = on the front of this final string and you should have your working formula.
Re: Exporting a dashboard as a PDF
Additional note I just now learned (I know this thread is very old, but it comes up early in google searches even in late 2021...): The color of widgets and such is part of 'Background Graphics', so just make sure that box is checked when you go to print, and it'll look the same as it does on your Dashboard :)
Re: ISBLANK, IF, AND combination
So using plain language: IF Customer Owner is NOT BLANK AND Start Date is Less Then Today AND End Date is Greater Then Today then Check
So... IF(AND(NOT(BLANK(Expression1)), Expression 2, Expression 3), True, False)
Try This
=IF(AND(NOT(ISBLANK([Customer Owner]@row)), [Start Date]@row < TODAY(), [End Date]@row > TODAY()), 1, 0)
Be careful about your <... you may want them as <=
In the expression, I have written if it was to start on today's date it would not show checked..
=IF(AND(NOT(ISBLANK([Customer Owner]@row)), [Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), 1, 0)
Would include today's date as within the start and end date rage
Re: Divide By Zero Error
Hi @LeAndre P
It looks like you've just added the 0 portion of the formula after the wrong closing parentheses!
An IFERROR Function works like this:
=IFERROR(Formula, 0)
Notice how it wraps around the entire formula and then you put the 0? Your current formula as the 0 inside of the COLLECT function which is why you're getting an error.
Try this:
=IFERROR(AVG(COLLECT([PO Total]:[PO Total], [Order Date]:[Order Date], >=DATE(2021, 12, 1), [Order Date]:[Order Date], <=DATE(2022, 12, 31))), 0)
Cheers,
Genevieve
Re: Subtracting Dates
It is because you have two dates entered. If you were to enter the same date as start and finish, then it would calculate as one day. Think of it more as duration than difference. If you wanted the difference, you can just subtract the dates.
=[Finish Date]@row - [Start Date]@row
Re: Automated Workflow Stopped Working
Hi @AllieF
Hope you are Good and Safe!
Have you tried by scheduling the workflow or once you changed the component type and Saved try to refresh it and Check sometimes bug issue will happen.
Thanks,
Sandhiya P
Re: INDEX & MATCH using multiple criteria
Hi @Brent Guhl
It looks like you have a closing parentheses too early in your formula! You'll want the COLLECT function to contain all of your columns and criteria, then end off with another 1 in the INDEX function to identify you want the first row match to be brought back, like so:
=IFERROR(INDEX(COLLECT({Intake Date of Case}, {Intake Email}, Email@row, {Intake Multiple Cases}, 1), 1), "Email not on Intake")
Would you mind clarifying your last sentence? I presume your {Intake Multiple Cases} is the flag column and you're looking for only the row that's flagged (or 1), which will indicate that it's the most recent out of all of the duplicates. Is that correct, or do you also have a date column that you need to evaluate?
It would be helpful to see screen captures of both sheets with sensitive data blocked out, if possible.
Cheers,
Genevieve