Hello all -
For an automated email output, I am attempting to create rules that would color a cell either red, yellow, or green based on values of the cell, within given parameters.
My input table contains 4 SKU numbers for each warehouse, and the amount on hand. So, let's say that for NJ1, SKU 16 needs to be green if the OnHand value is >=7, yellow if 4-6, and red if <=3. Each warehouse has specific parameters for what values fall within each color.
I have become somewhat familiar with table config (can color full rows or columns), but I'm struggling with how and where to enter my if/elseif's for this more complex task. I am new to writing the formulas, and so far, I just keep getting errors. Can someone please assist with how this formula should look in the row/column rules? Can this even be done?
Thanks in advance!
WHSE | SKU | OnHand |
NJ1 | 16 | 14 |
NJ1 | 20 | 22 |
NJ1 | 25 | 8 |
NJ1 | 33 | 4 |
CA1 | 16 | 2 |
CA1 | 20 | 12 |
CA1 | 25 | 8 |
CA1 | 33 | 7 |
OH1 | 16 | 14 |
OH1 | 20 | 10 |
OH1 | 25 | 9 |
OH1 | 33 | 5 |
Solved! Go to Solution.
Hi @MCDR929
What I tend to do is create a column that I can use for my colours before the table tool.
So build the logic you need using the standard set of tools.
Then when you are in the table tool, look to this column and see if its set to Red, Yellow or Green and build it upon that.
Not only do I find this easier to implement more complex rules, it also more transparent within the workflow for others (myself in 6 months) to see, and not buried away in row/columns rules.
Thank you for that @JoeS . Do you happen to have any samples I can see? I guess I'm unsure exactly what you mean when you say you create a column before the table tool.
@JoeS - this makes sense now. Thanks so much.
Now I just need to figure out how to plug in a range instead of just >=, etc. Thanks for the help!
The range should be covered due to the way the if statement works.
The bands for the amber and green are the low number.
So the example you listed above is implemented, the rest I just made up.
Edit: If that is an acceptable solution, if you could mark it as that, that would be great as then anyone else looking should be able to find it easier.
Perfect - I get it now. Thanks so much for the help!
Great stuff and not a problem.
Glad it was what you needed.
This is great! But how does one actually output this as a table in excel? I heard its quite a bit of faff
Use the render tool
User | Count |
---|---|
35 | |
27 | |
8 | |
7 | |
7 |