excel script question -- numbers to words
 

If you mean you have a list of words/phrases and they each have
corresponding number, you could just use a lookup table (i.e. using th
VLOOKUP() function). Search for VLOOKUP in the Help file and tha
should get you started.



--
Message posted from http://www.ExcelForum.com

excel script question -- numbers to words
 

hi all

i know very little about excel so i don't even know if this is possible

i would like to be able to fill in a sheet wit numbers and the numbers then
get translated into a prescribed word or phrase. I have over 50 phrases and
will need to do it over 10 times (diferently each time)

is there a way to get the numbers to translate to words as i type or a global
'update' to tranlate?

hope someone can help

thanks
bronach

excel script question -- numbers to words
 

You would need a VBA macro to automatically translate what you type into
something else. If you are having problems with worksheet functions, macros
should probably be reserved for later, when you are more "fluent" with Excel.


On Wed, 11 Aug 2004 19:40:38 GMT, bronach wrote:

On Wed, 11 Aug 2004 18:58:09 +0100, kkknie wrote
(in message ):
If you mean you have a list of words/phrases and they each have
acorresponding number, you could just use a lookup table (i.e. using
theVLOOKUP() function). Search for VLOOKUP in the Help file and thatshould
get you started.

K---Message posted from http://www.ExcelForum.com/

thanks for the quick reply

hmm , not sure. what i want is to be able to type a bunch of numbers in one
sheet in different columns (representing nesting) and instead of the number
the word appears ... am i making myself clear? i had a look at vlookup, but i
can't undersatand it.

i think it might be right but the explanation of how VLOOKUP works only
confused me.

bronach

excel script question -- numbers to words
 

In a generous mood today, so here goes:

Paste the following code in the worksheet code module for the sheet yo
will be entering the numbers. (Right click on sheet tab and select vie
code to get there.)

Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 Then 'Only one cell changed
If Target.Row = 1 And Target.Column = 1 And Target.Column <= 10 Then 'A1 through A10
Application.EnableEvents = False 'Disable events
Target.Value = Application.WorksheetFunction.VLookup(Target.Value , Sheets("Sheet2").Range("A1:B4"), 2)
Application.EnableEvents = True 'Reenable events
End If
End If

End Su
-------------------

This is an event procedure which will run whenever a cell is changed o
this worksheet. I'll take you through it.
1. Check to see if only one cell was changed (count=1)
2. Check to see if in the range A1-A10. You can change this b
changing the .Row and .Column values to fit into your desired range.
3. Disable events. Since we are changing a cell here, we don't wan
this event to run again.
4. Target.Value is the changed cell's value. Apply the VLooku
function on it using a table on another sheet. I'll explain this a bi
more later.
5. Turn event handling back on so it will work next time too.

The way VLOOKUP works in your case:

=VLOOKUP(LookupValue, LookupTable, ReturnColumn)

The lookup value is the number you entered.
The lookup table is a set of two columns with the numbers in one an
the return values (words/phrases) in the next. The table shoud b
sorted by number.
The return column in your case would be 2 (the column with th
words/phrases).

To change my code to handle your case, you will first need to creat
your lookup table on another sheet. Then replace Sheets("Sheet2") wit
whatever your sheet name is where the lookup table resides. Replac
Range("A1:B4") with the range of your lookup table.

Post back with questions,



--
Message posted from http://www.ExcelForum.com

excel script question -- numbers to words
 

On Wed, 11 Aug 2004 18:58:09 +0100, kkknie wrote
(in message ):
If you mean you have a list of words/phrases and they each have
acorresponding number, you could just use a lookup table (i.e. using
theVLOOKUP() function). Search for VLOOKUP in the Help file and thatshould
get you started.

K---Message posted from http://www.ExcelForum.com/

thanks for the quick reply

hmm , not sure. what i want is to be able to type a bunch of numbers in one
sheet in different columns (representing nesting) and instead of the number
the word appears ... am i making myself clear? i had a look at vlookup, but i
can't undersatand it.

i think it might be right but the explanation of how VLOOKUP works only
confused me.

bronach