Simple Formulas

[draramis]

If I have two columns, side by side with one column with niumbers from 1 to 26, and the other with the alphabet, what formula would I need to write, so that when I type in a number in a particular cell, the formula will place the letter that corresponds to that number in the cell below the number typed in (e.g. I type in 17 and the cell below this indicates Q)?

[tinman44]

I'd use VLOOKUP. See att'd.

[User_5]

Here's a way I came up with very quickly.

If you haven't already done so, type 1-26 in a column and A-Z in the very next column.
Select the range that stores these values and name the range "a". (Insert, name define or
check the online help on how to name a range.)

In a cell somewhere in the top row enter this formula and you'll get a #REF returned,
which is good.

Code: Select all

   =INDEX(a,OFFSET(H1,-1,0),2)


Enter a number 1-26 in a cell somewhere and in the next cell below, copy and paste the .#REF formula or you can type it in by hand.
You should get the result you want.

What's good about getting #REF when you enter the formula in the top row the formula is that it will be easy to recognize where the formula is.

If you use the formula in any other cell, you can copy and paste it from that cell to wherever you want, without having to copy from the top row.
You can use it even if you're in anothr worksheet. Just copy and paste the formula in that worksheet.

If you paste the formula in a cell and the cell above doen't yet have a number, you'll get a #Value error.
Just type a number in the cell above and you'll get the correct result.

You can always just type the formula in a cell instead of copying and pasting but its more time-consuming.
Someone may come up with a better way, which is ok by me.

I can show you the VBA code but since you didn't post it in that topic, I'm assuming you may not know how to paste code and run it.

[draramis]

Thanks for the help! Appreciate it.