How to display a design in a cell (formula based) to be chosen from either of 3 designed cells

[hsandeep]

1. I have 2 columns with numerical values; say A1 = 130.25 & B1 = 118.68.
2. I have designed 3 arrows (representing UP, DOWN & EQUAL) & have inserted in 3 separate cells, say, R1, S1 & T1.
3. C1=A1-B1 (formula).
4. I want corresponding arrows to be grasped (from either R1, S1 or T1) based on the result in C1 & shown in cell C1. It has to be à If C1=+ve then get R1, -ve get S1, =0 get T1. HOW TO DO THIS?
5. A1 & B1 are DDE feeds & keeps on changing frequently, thus cell C1 should also keep its pace. I am using excel2007.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks in advance. Please do help. Please.
<o> </o>

 

[Peter_SSs]

A clever thought Glenn. I certainly hadn't thought of that.

 

[hsandeep]

Glenn,
3 Cheers. It works & that too without using codes. As such my workbook is overloaded with other codes.
Can you give me some more color ‘names’ other than’ Green’ & ‘Red’ which can be used in the formula you had provided? I found 5 more colors à ‘Blue’, ‘Black’, ‘White’, ‘Magenta’, ‘Yellow’ also works. I need lot of colors which will depend on how much the difference in the value has increased in the cell C1=A1-B1.
Sandeep

 

[GlennUK]

There is only one more, and that's Cyan.

But you are not going to be able to apply multiple colours by Number Format .... there are only 3 conditions that you can apply that, i.e. a maximum of 3 colours.

 

[Peter_SSs]

By name, yes, there are only 8. By number there are 56. In your custom format use the syntax
[Color n]
where n is an integer from 1 to 56

 

[hsandeep]

Peter,
What you have suggested is better. A small hurdle F How to change the cell’s (containing this formula) background color USING A FORMULA depending on 1<SUP>st</SUP> ‘n’ (being from 1 to 56) in [color n]. Currently, I have started doing it manually only. Reason: Some arrow’s color although useful becomes hazy because of a single background color. It would be like F background colors would be of 1 to 56 different color shades depending on ‘n’ of the 1<SUP>st</SUP> [color n] in the formula; where n=1 to 56.
I am using Microsoft Office 2007 suite.
I would be more happy if further advanced background coloring can be done in ANOTHER cell (say D1) of the same workbook using some ‘formula’ or ‘any method’ which should use Red=r, Green=g & Blue=b where r, g & b =0 to 255. The combination of rgb values may be determined (using Excel formula) and may be put in 3 different cells i.e. this r, g & b would derive their values depending on ‘n’ of the 1<SUP>st</SUP> [color n] in the formula in the cell C1.
Sandeep

 

[Peter_SSs]

How many arrows are you using? Have you considered using Conditional Formatting with a 5-arrow Icon Set? That gives you 2 different up arrows, 2 different down arrows and a 'no change' arrow.

You cannot change the backgroung colour of a cell with the formula in the cell. You would need to use vba or Conditional Formatting. You can have as many Conditional Formats as you like in Excel 2007.

 

[GlennUK]

By name, yes, there are only 8. By number there are 56. In your custom format use the syntax
[Color n]
where n is an integer from 1 to 56

Wow, I've learned something today! Thanks.

 

[hsandeep]

Peter,
I really appreciate your visibility in asking me this question. I’ll try to explain.
I my worksheet, the cell in which I want to display these ‘arrows’ of different colors OR use different background colors in the cell à contains a formula with either of the following 9 results:
If the cell value is:
Between 0 and 100 Display Arrow1 or Background color1
Equal to 100 Display Arrow2 or Background color2
Between 100 and 200 Display Arrow3 or Background color3
Greater than or Equal to 200 Display Arrow4 or Background color4
Between 0 and -100 Display Arrow5 or Background color5
Equal to -100 Display Arrow6 or Background color6
Between -100 and -200 Display Arrow7 or Background color7
Less than or Equal to -200 Display Arrow8 or Background color8
Equal to 0 Display Arrow9 or Background color9
The goal is F To get a display so that I can understand immediately that Out of 9 possible outcome, what is the current result of the cell.
Sandeep

 

[Peter_SSs]

With 9 different formats required, I would be just using background colour with standard Conditional Formatting. Here's a sample with the CF for C1 shown.

Excel Workbook
	A	B	C
1	100	305	-205
2	120	285	-165
3	140	240	-100
4	160	245	-85
5	180	225	-45
6	200	200	0
7	220	185	35
8	240	165	75
9	260	160	100
10	280	125	155
11	300	105	195
12	320	85	235
13	340	65	275
CF hsandeep

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	1. / Formula is =C1>=200	Abc
C1	2. / Formula is =C1>100	Abc
C1	3. / Formula is =C1=100	Abc
C1	4. / Formula is =C1>0	Abc
C1	5. / Formula is =C1=0	Abc
C1	6. / Formula is =C1>-100	Abc
C1	7. / Formula is =C1=-100	Abc
C1	8. / Formula is =C1>-200	Abc
C1	9. / Formula is =C1<=-200	Abc

 

[hsandeep]

Peter,
I am trying your earlier suggestion of using conditional formatting.
I will ‘initially’ be happy even if following 3 outcomes results into:
If the cell value is:
Greater than 0 Display Green Light
Equal to 0 Display Yellow Light
Less than 0 Display Red Light
Here I want F to format the cell using CF | Icon Sets | 3 Traffic Lights (Unrimmed).
How to do this?
Secondly, can the cell’s value can be displayed ALONGWITH the above lights ALSO? If yes, please do help.
Sandeep