Hi All,
I am mapping a spreasheet table to my excel sheet. One row has conditional formatting set, so that when the values are less than 5, it will show Green fill.
This works fine in excel sheet however when I preview it is not shwing in the Dashboard. Meaning, the spreadsheet component is not carryng over this change.
is it possible at all , i am doubting that this component moghtnot do this.
I tried with Scorecard and same effect. It is not showing the color fill as per excel sheet.
Pls see attached.
–use component with Alerts for “conditional formatting”
Conditional formatting is a VBA based event in Excel…anything based on VBA cannot be translated into HTML5/SWF files…thus VBA events (macros, etc) will never work in the SWF file.
Thanks, the spreadsheet component has the Alerts available but it is by columns and my requirement is different on different rows, so under one column I may have different rqrmt.
For example, Cell A5 should be Green if value is less than 6, but Cell A6 should be Yellow if value is >4.
edit: deleted some text because I misunderstood your post.
More edits:
Here is the right answer:
You can pull this off…you just need to use a very complex formula system…usually with And() or Or() functions in excel. Your alert values area has nothing to do with your charted range.
Ill repeat that. Your alert values area has nothing to do with your charted range.
You need to set up a system of logical functions in a single column separate from your chart range. Each single cell in that column can have its own special formula to turn the scorecard cell a certain color.
If you are doing a stoplight scorecard then you have to make sure that all your logical formulas in these cells return a 1, 2, or 3.
There is no possible way you cannot solve any logic problems with
some combination of the following 3 functions.
Nested IF’s ()
Or()
and()
You alert range is looking for 1,2,or3. It doesn’t matter how the values got there.
Cell B5 = if(a5 < 4, 1, 0)
cell b6 = if(a6 < 4,1,if(a6<6,2,0))
The alert range is based on column B...not A. The alerts are config'd for 1,2, or 0.
ect.
If you arent understanding what im talking about…then grab your local excel expert to assist with your excel functions.
Thanks, this is also the solution that I got to finally. I had to set up another range formula, which was then mapped to the Alerts in Scorecard.
Only problem i had was that I have some text fields in the middle and not all rows are populated with number but some are blank or show text. Under the current scenerio they show those rows with colors too…
you should not be returning text in your alert range. Your IF functions should be taking care of that part.
In the scorecard alert there is also a config option for ‘no data’ as well as the normal color shading. No data should probably return a white background…not a color one.
Yes, i see the NO data icon, but I haven’t figured out a way to activate it. So far clicking on it has not worked. I am not sure how to make it work so that when a cell is “” empty, then keep it white background…
(BOB member since 2009-12-31)
(BOB member since 2002-06-20)
(BOB member since 2008-06-05)