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>

 

[GlennUK]

So what is inserted in R1 S1 and T1 exactly? Are they cell contents, or objects located over those cells? Or something else?

 

[Peter_SSs]

If they are cell contents, try (edited)

=CHOOSE(SIGN(A1-B1)+2,S1,T1,R1)

 

[hsandeep]

If they are cell contents, try (edited)

=CHOOSE(SIGN(A1-B1)+2,S1,T1,R1)

Peter,
It works. But I’ll try to express myself more clearly. It works & C1 is able to get the values from R1, S1 or T1 provided they (R1, S1 or T1) are ALPHABETS or NUMERICAL or ALPHANUMERICAL.
In R1, S1 & T1, I had used Insert|Shapes|chosen from ‘Block Arrows’ 3 arrows (UP, DOWN & EQUAL) & then had filled some colors, Line Color, Line Style using à ‘Format Shape’.
Is it possible to get any of the 3 arrows in the Display Cell C1 depending upon its formula’s result?
It has to be if C1>0, get R1, if C1<0, get S1 or if C1=0, get T1.
Technically, I do not know exactly what word I should use: Content or Laid, so I had tried to express myself as above.
I need to show in C1 either of 3 arrows. They may be ‘Laid’ (if I am using this word correctly here) In R1, S1 & T1 but I do not know how to lay them. You may please help me here if this solves the problem.
Sandeep

 

[hsandeep]

So what is inserted in R1 S1 and T1 exactly? Are they cell contents, or objects located over those cells? Or something else?

Glenn,
I had replied below. Please see & help.
Sandeep

 

[Peter_SSs]

In R1, S1 & T1, I had used Insert|Shapes|chosen from ‘Block Arrows’ 3 arrows (UP, DOWN & EQUAL) & then had filled some colors, Line Color, Line Style using à ‘Format Shape’.
Is it possible to get any of the 3 arrows in the Display Cell C1 depending upon its formula’s result?
It has to be if C1>0, get R1, if C1<0, get S1 or if C1=0, get T1.
Technically, I do not know exactly what word I should use: Content or Laid, so I had tried to express myself as above.
I need to show in C1 either of 3 arrows. They may be ‘Laid’ (if I am using this word correctly here) In R1, S1 & T1 but I do not know how to lay them. You may please help me here if this solves the problem.
Sandeep

I think you are going to struggle to do it directly this way. A couple of alternatives to consider ...

A. Use Wingdings font like you did in your post. Format C1 as Wingdings font and use this formula in C1. You then don't even need R1:T1.
=CHOOSE(SIGN(A1-B1)+2,"é","è","ê")

B. Have your shapes as you described in R1:T1 and use this code. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Make a change to A1 or B1.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> sSource <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("A1:B1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> IsNumeric(Range("A1").Value) And <SPAN style="color:#00007F">Is</SPAN>Numeric(Range("B1").Value) <SPAN style="color:#00007F">Then</SPAN><br>            ClearShapeInC1<br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Range("A1").Value - Range("B1").Value<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> > 0<br>                    sSource = "R1"<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 0<br>                    sSource = "S1"<br>                <SPAN style="color:#00007F">Case</SPAN> Is = 0<br>                    sSource = "T1"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            Range(sSource).Copy Destination:=Range("c1")<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            ClearShapeInC1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> ClearShapeInC1()<br>    <SPAN style="color:#00007F">Dim</SPAN> sh <SPAN style="color:#00007F">As</SPAN> Shape<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sh <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Shapes<br>        <SPAN style="color:#00007F">If</SPAN> sh.TopLeftCell.Address = "$C$1" <SPAN style="color:#00007F">Then</SPAN><br>            sh.Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> sh<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[GlennUK]

Glenn,
I had replied below. Please see & help.
Sandeep

Hi Sandeep,

it's very easy to do this directly, using defined names and a picture object.

Firstly assign defined names to the 3 cells R1, S1, and T1, such as UPARROW, DOWNARROW and EQUALS.

Now create a defined name of PICTURECHOICE with a definition of:

=IF(Sheet1!$C$1>0,INDIRECT("UPARROW"),IF(Sheet1!$C$1<0,INDIRECT("DOWNARROW"),INDIRECT("EQUALS")))

( assuming this is all in Sheet1 ... adjust to your case )

Now generate a picture object ... the easiest way is to use the camera tool, by taking a picture of any cell in the sheet, and drawing a picture object over cell D1. The picture will have an assignment ( see the formula bar ) pointing to the cell ref of the picture taken ... alter this to be =PICTURECHOICE

That's it.

 

[hsandeep]

Peter,
Yes both A & B had worked.
Thanks Peter for your kind gesture. But please would you let me know technically what is difference between “Content” or “Laid” in a cell. This may help me.
Sandeep

 

[Peter_SSs]

Where did you get the term "Laid", as far as I know I didn't use it?

 

[GlennUK]

Hi Peter, by the way, a variation on your method A is to specify the WingDing chars in a custom number format, and you can colour at the same time that way :
Have =A1-B1 in a cell, format number format as Custom with a format of:

[Green]"ê";[Red]"é";"è"

and format the cell font as WingDings.

Just thought you might like to know that method too.