Best Of
Re: Is there a way to show the sheet name in calendar view of a report?
You can't include the sheet summary in the JOIN function. It has to be added before or after.
Make sense?
Re: Is there a way to show the sheet name in calendar view of a report?
Happy to help!
Try something like this.
=[Sheet Name]#
Did that work?
✅Remember! 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
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