Maintaining Text Formatting in a Lookup
 

I've searched high & low on this one & the general consesus I've found
is that it simply can't be done?... Has anyone any bright idea's on the
following?

I've assigned star symbols to the grades as follows:

A: ***** (5 green stars)
B: ***** (4 green stars, 1 white)
C: ***** (3 green stars, 2 white)
D: ***** (2 green stars, 3 white)
E: ***** (1 green star, 4 white)
F: ***** (5 white stars)

I want to use vlookups to return the star values in the corresponding
cells for each grade.

Eg. A grade to show ***** etc.

The formating of the star colour is the important thing here --
Apparently there's no way of copying formats with formula's in excel??

Has anyone any bright idea's how you might get around this?

Is it possible to automatically replace characters with autosymbols?

Conditional formating is no use as there's only 3 conditions & you
would have to format the whole cell regardless instead of just
particular characters in the cell.

Any help/suggestions would be much appreciated.

Thanks!! :confused:


--
loscherland
------------------------------------------------------------------------
loscherland's Profile: http://www.excelforum.com/member.php...fo&userid=6709
View this thread: http://www.excelforum.com/showthread...hreadid=534605

Maintaining Text Formatting in a Lookup
 

Hi Loscherland,

I'm not an expert but i've done something similar. Instead of using lookups,
I use the same criteria in an if or select case statement and then copy the
contents of the cell, i then put the whole code on a 'update' button which i
then press.

So for example say you have the

A ***** 5 green stars is =90% in cell A1
B ***** 4 green + 1 white is =80% and <90% in cell A2
and the grade you want to compare is in cell B2 and you want to put the star
thingy in C2

something along the lines of (my vba isn't that great and i dont have the
code to hand but you get the idea) If B2 =0.9 then cells("A1").copy;
cells("C2".).paste else cells("A2").copy; cells("C2".). You'll have to mess
about a bit to 1. get the vba right and 2. get the right cell reference by
using a variable instead of "C2" etc but i hope the general idea helps.

Emma

"loscherland" wrote:


I've searched high & low on this one & the general consesus I've found
is that it simply can't be done?... Has anyone any bright idea's on the
following?

I've assigned star symbols to the grades as follows:

A: ***** (5 green stars)
B: ***** (4 green stars, 1 white)
C: ***** (3 green stars, 2 white)
D: ***** (2 green stars, 3 white)
E: ***** (1 green star, 4 white)
F: ***** (5 white stars)

I want to use vlookups to return the star values in the corresponding
cells for each grade.

Eg. A grade to show ***** etc.

The formating of the star colour is the important thing here --
Apparently there's no way of copying formats with formula's in excel??

Has anyone any bright idea's how you might get around this?

Is it possible to automatically replace characters with autosymbols?

Conditional formating is no use as there's only 3 conditions & you
would have to format the whole cell regardless instead of just
particular characters in the cell.

Any help/suggestions would be much appreciated.

Thanks!! :confused:


--
loscherland
------------------------------------------------------------------------
loscherland's Profile: http://www.excelforum.com/member.php...fo&userid=6709
View this thread: http://www.excelforum.com/showthread...hreadid=534605

Maintaining Text Formatting in a Lookup
 

Thanks Emma!

I don't think it's gonna be ideal for what i was looking to do but it's
an interesting idea all the same.

By now I've given up on the idea & intead resorted of substituting the
coloured star symbols for boring black & white circles!... a little
something like this...

*****
****O
***OO
**OOO
*OOOO

It's not gonna win any art competitions but it'll do!!! :)


--
loscherland
------------------------------------------------------------------------
loscherland's Profile: http://www.excelforum.com/member.php...fo&userid=6709
View this thread: http://www.excelforum.com/showthread...hreadid=534605