Help with Bulky Formulas/Formatting?
Hi all,
I'm fairly new to smartsheet (with no Excel experience either). Here's my situation.
I'm tracking POs-- first, you enter in a PO amount, and in subsequent columns what you invoice per-month. There's a final "Totals" column that sums up your monthly invoices.
Currently, I have a hidden column set up to test if PO Total = Totals, using this formula:
=(IF ([PO Total]2 = Totals2, "1", "0"))
Then I have Conditional Formatting set up (see screenshot).
Without the first line of conditional formatting, rows with Blank POs that match Black Totals will highlight green and it is just confusing.
My question is... is there any way to clean this up and condense it? Because I'm not expert level, I feel like all of this is clunky, and setting it up on 8+ sheets is a bit time consuming.
Any insight would be extremely appreciated!
Cheers!
Comments
-
Hi,
You could add an ISBLANK function at the beginning of your formula.
Would that work?
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Also: Pro tip!
You can reorder the conditional formatting rules. They rank from top to bottom.
Hope that helps!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Additionally, you can use the "Save as New" feature on the sheet to essentially duplicate it. Then you would just have to make a few minor tweaks to the details instead of having to rebuild from scratch 8 times.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Andree,
Thank you!
How would you structure that with my current one? I am still familiarizing myself in SmartSheet formulas?Cheers!
-
I'll definitely do this if I'm making more-- sadly, these are ones that have been premade and I am going back in and adding these rules!
-
Did you try having the 2 conditional formatting line as multiple conditions like the below? If not, see if that works for you.
If [PO_Total(update PO total when PO is revised] is not blank and [Po/Actual Total Check] is 1 they apply this format....
-
I had not done this, but this takes out an annoying step! Thanks!
-
Happy to help!
Try something like this.
=IF(ISBLANK([PO/Actual Total Check]@row); ""; IF([PO Total]@row = Totals@row; 1; 0))
The same version but with the below changes for your and others convenience.
=IF(ISBLANK([PO/Actual Total Check]@row), "", IF([PO Total]@row = Totals@row, 1, 0))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work/help?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!