Hi All,
I have a scorecard created in excel 2007. All my end users are using excel 2002. Now I want to build a scorecard in Excel 2002 like what we have in excel 2007. Is it possible.
I want to display the traffic signals and 5 level indicators (arrows), based on conditional formatting.
This is out of box in excel 2007. But how do I achieve this in excel 2002?
Is there a simple method? Please suggest...
Thanks in advance
Sri
A lot depends on the complexity of the conditions.
You maybe able to use webdings, or similar, font and conditional formatting.
Or shapes and VBA code.
http://www.andypope.info/tips/tip011.htm
Hi Andy,
Thanks for your response. Now in excel 2007, we have an arrow which represent silghtly off target (the angular down arrow) and slightly ahead of target (the angular up arrow). Do we have the same in Wingdings font? And what about the traffic signals (red, amber, green)? Do we have an oval representing them?
Thanks again
Sri
Use the Character map application in Windows to see what characters are available for any given font.
These are all the arrows for wingdings
çèéêëìíî
for oval in traffic light you can use
l
Thanks a lot. I think this will do
Regards
Sri
Hi Andy,
As suggested , i have tried using the wingdings font. I am not facing any issues with arrows. But when I use the oval and give three conditions in conditional formatting, I can see that it is not working properly, means when I give green and amber for the first two conditions, it is working fine, but when I add the third condition red -> it is not working ie, the green and amber still work but red is not working.
Any siggestions?
We need to see how you have set up the cell and the CF
can you post example file
Here is a sample attached. Please let me know where I am going wrong.
Thanks
If you place the formula you have used in 3 cells you will see how the CF is working and why the colour used may not be want you expect.
The problem is the second formula is returning TRUE when in fact it should be FALSE. The reason is the formula you have used.
So rather than the current
=$B$5<4&$B$5>=2.5
Use this instead.
=AND($B$5<4,$B$5>=2.5)
Hi Andy,
Just realized, I have a formula that is being referenced some thing like this:
=CalcScore(BB55,AF55,IF(AG55<>0,AG55,AH55),BC55). This formulae is defined in a macro.
The final output is displayed a %. For example 400%
I am using this macro
Set IB1 = Range("AI29")
IB1.FormulaR1C1 = _
"=CalcScore(R[26]C[19],R[26]C[-3],IF(R[26]C[-2]<>0,R[26]C[-2],R[26]C[-1]),R[26]C[20])"
Range("AI29").Select
Selection.AutoFill Destination:=Range("AI29:AI33"), Type:=xlFillDefault
Range("AI29:AI33").Select
For Each Cell In x.Range("AI29:AI33")
If Cell.Value <> "" Then
If (Cell.Value / 100) >= 4 Then
x.Cells(Cell.Row, Cell.Column + 1).Font.ColorIndex = 31
x.Cells(Cell.Row, Cell.Column + 1).Value = "l"
End If
If Cell.Value < 4 Then
If Cell.Value >= 2.5 Then
x.Cells(Cell.Row, Cell.Column + 1).Font.ColorIndex = 30
End If
End If
If Cell.Value < 2.5 Then
x.Cells(Cell.Row, Cell.Column + 1).Font.ColorIndex = 27
End If
End If
Next
This is giving me the 400% as output, but I want to display the traffic light.
Could you please let me know how I can proceed to display the traffic light red, amber, green.
Please help.
Thanks
Last edited by sridhar.voleti; 06-17-2009 at 08:03 AM.
Can you not just use that as is?
I did a quick test and CF appears to work with UserDefinedFunctions.
If not you will need to place the UDF in a cell and then reference that cell
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks