#### nooraali

##### New Member
I have to solve this cryptogram by providing formulas in each position of the secret message (with the formulas being able to carry over in each row of the message).

In the secret message, the numbers 2 to 34 have replaced letters and punctuation marks. The key to the cryptogram is in column A, where either a numeric value or color label is listed for each of the numbers in the secret message. The decode tables to the right are for finding which letter or punctuation mark is being represented in the secret message.

Sorry I'm still very new to excel spreadsheet formulas and have never solved a cryptogram before! I know that I have to use the ISNUMBER function but dont know where to start! Thank you very much for the help!!

[/URL][/IMG]

Last edited:

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### oldbrewer

##### Well-known Member

<tbody>
</tbody>
34 = brown = Y
6= 104 = E
20 = orange = s
2= yellow = tlooks a bit like YESTERDAY
2yellow100AREDR
3105101BORANGES
4116102CYELLOWT
5gray103DGREENU
6104104EBLUEV
7114105FINDIGOW
8silver106GVIOLETX
9102107HBROWNY
10green108IBLACKZ
11113109JWHITE.
12maroon110KPINK!
13109111LGOLD?
14pink112MGRAY@
15115113NSILVER#
16gold114OMAROON,
17106115PIVORY
18red116QMUSTARD&
19103
20orangeTHE TABLE BELOW IS CALLED MYTABLE
21indigo
22black
23111100Ataking the first number in the message = 34
24100101B
25101102C=vlookup(34,firsttable,2) gives BROWN
26ivory103D
27blue104E=vlookup(BROWN, mytable,2) gives Y
28107105F
29108106G
30mustard107H
31white108I
32110109J
33112110K
34brown111L
112M
113N
114O
the table above is called firsttable115P
116Q
BLACKZ
BLUEV
BROWNY
GOLD?
GRAY@
GREENU
INDIGOW
IVORY
MAROON,
MUSTARD&
ORANGES
PINK!
REDR
SILVER#
VIOLETX
WHITE.
YELLOWT

<colgroup><col span="16"></colgroup><tbody>
</tbody>

#### nooraali

##### New Member
Thank you! but does that mean that I have to rewrite the formula for every cell?

#### nooraali

##### New Member
hello i have just tried to apply this to excel and it is not working why do you think this is?

#### mole999

##### Well-known Member

If you take 28 it equals 107

107 equals the letter H

so 28 = H

you need to solve for all parts to establish the quote by 13th Century Persian Rumi

#### nooraali

##### New Member
I understand that i can just do this manually for each cell, but how can I come up with formulas that will carry over in each row of the message? thanks!

#### mole999

##### Well-known Member

surely this is a test of your knowledge and excel skills as part of course work. If a forum provided a perfect answer do the helpers all get credits

#### mikerickson

##### MrExcel MVP
Imagine a column B, where B2 has the formula

=IFERROR(VLOOKUP(A2, \$X2:\$Y\$18,2,FALSE),VLOOKUP(A2, \$AA2:\$AB\$18, 2, FALSE))

Then consider putting =INDEX(\$A:\$A, D5, 1) in C5

#### oldbrewer

##### Well-known Member
B+ , Mike

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,037
Messages
5,856,960
Members
431,841
Latest member
jaybeem

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back