Insert symbol after drop-down is made

[Roady]

I have three columns for product approval. In each one, you can select either
'Approved', 'Rejected', or 'Dropped'. For example, sometimes a product will
be rejected in the first column and then approved on the second attempt, so
then the 2nd column will show as 'Approved'.

I want to be able then have it auto-insert a symbol based on the selection
chosen the most recent approval column as follows:
Approved - check mark
Rejected- big 'X'
Dropped- down arrow
Note: if the approval column has no entries in it, I would like the cell to
be blank (no symbol)

Is there a way to do this? Does Excel work with symbols in this way?
Thanks!

 

[Pete_UK]

What you can do is return a character which has the symbol you want in
a sybol font, like Wingdings. For example, CHAR(234) is a bolded down
arrow in Wingdings, CHAR(251) is a cross, and CHAR(252) is a tick. So,
the cell is formatted using Wingdings, and the formula returns the
appropriate character.

Hope this helps.

Pete

 

[Roady]

Great, thanks, Pete! So it sounds like I would then build that CHAR number
into the formulas for returning the appropriate character- yes?

How can I get a list of all the Wingdings and their corresponding character
numbers?

Thanks!

 

[Gord Dibben]

Insert>Symbol>Symbol

Select Wingdings from Font and lower right select "decimal"

Each symbol has a number.

You can download an ascii table workbook from Jon Petier's site to see a
list of symbols and their numbers.

http://peltiertech.com/Excel/tricks.html#ascii


Gord Dibben MS Excel MVP

 

[Roady]

Thanks for the link. It appears that there is no way to generate a blank cell
in Wingdings- is that accurate? The reason I ask is because I am getting an
error in my formula. I want a blank cell to return a blank cell in my
VLookup.

 

[Pete_UK]

To answer your last question first, in a blank Excel sheet put this
formula in A1:

=CHAR(ROW())

then copy it down to row 255. Highlight all those cells, and format
using the Wingdings font - you can see the character number (as the
row identifier) against each character. You can easily try other
symbol fonts to see what they look like.

Now back to the original question - assume A1, B1 and C1 are the
dropdowns. In another area of the sheet (eg X1:Y30) you can set up a
table like this:

AAA 252
AAR 251
AAD 234
ARA 252
ARR 251
ARD 234
RAA 252
RAR 251
RAD 234
RRA 252
RRR 251
RRD 234
DAA 252
DAR 251
DAD 234
DRA 252
DRR 251
DRD 234
AA 252
AR 251
AD 234
RA 252
RR 251
RD 234
DA 252
DR 251
DD 234
A 252
R 251
D 234

which represents all possible combinations of the first letters of the
three columns and the character code that you want for each
combination (based on the last letter). Then in D1 you can put this
formula:

=IF(A1&B1&C1="","",CHAR(VLOOKUP(LEFT(A1)&LEFT(B1)&LEFT(C1),X$1:Y
$30,2,0)))

Format the cell as Wingdings, then try it out by changing the values
in A1, B1 and C1.

The table and VLOOKUP formula avoids having multiple IFs.

Hope this helps.

Pete

 

[Pete_UK]

Yes, you can have a blank cell - return either "" or " ".

Post your formula, so we can comment on it.

Alternatively, use the one I suggested (with the table).

Pete