Prev: how can i apply a footer in every .exl file
Next: To attach file to a workbook in a addin.
From: Walter Briscoe on 4 May 2010 02:05
I run Excel 2003 on Windows Vista.
I want to drive an HTML page with the following excerpt:
<select id="title" name="title" tabindex='0' onfocus='rememberMyFocus("
title");'>
<option value="">-- Please select --</option>
<option value="0001">Ms</option>
<option value="0002">Mr</option>
<option value="Z030">Mrs</option>
<option value="Z040">Miss</option>
....
</select>

I have a drop down list, populated from the first column in:
Titles Code
Ms 0001
Mr 0002
Mrs Z030
Miss Z040
....

In VBA code, I can easily translate titles to codes with something like
Private Function TranslateTitle(ByVal Title As String) As String
Select Case Title
Case "Ms"
TranslateTitle = "0001"
Case "Mr"
TranslateTitle = "0002"
Case "Mrs"
TranslateTitle = "Z030"
Case "Miss"
TranslateTitle = "Z040"
...
End Select
End Function

I prefer to do the translation in Excel, itself and have a cell formula
=VLOOKUP(B5, Sheet3!A2:B69, 2, False)

That does not use the offset in the named range I use for validation.
Is there a "better" way to do the translation?
--
Walter Briscoe
From: ozgrid.com on 4 May 2010 03:12
See this page for different ways.
http://www.ozgrid.com/Excel/cell-lookup.htm



--
Regards
Dave Hawley
www.ozgrid.com
"Walter Briscoe" <wbriscoe(a)nospam.demon.co.uk> wrote in message
news:cDsm5RBZk73LFwct(a)freenetname.co.uk...
>I run Excel 2003 on Windows Vista.
> I want to drive an HTML page with the following excerpt:
> <select id="title" name="title" tabindex='0' onfocus='rememberMyFocus("
> title");'>
> <option value="">-- Please select --</option>
> <option value="0001">Ms</option>
> <option value="0002">Mr</option>
> <option value="Z030">Mrs</option>
> <option value="Z040">Miss</option>
> ...
> </select>
>
> I have a drop down list, populated from the first column in:
> Titles Code
> Ms 0001
> Mr 0002
> Mrs Z030
> Miss Z040
> ...
>
> In VBA code, I can easily translate titles to codes with something like
> Private Function TranslateTitle(ByVal Title As String) As String
> Select Case Title
> Case "Ms"
> TranslateTitle = "0001"
> Case "Mr"
> TranslateTitle = "0002"
> Case "Mrs"
> TranslateTitle = "Z030"
> Case "Miss"
> TranslateTitle = "Z040"
> ...
> End Select
> End Function
>
> I prefer to do the translation in Excel, itself and have a cell formula
> =VLOOKUP(B5, Sheet3!A2:B69, 2, False)
>
> That does not use the offset in the named range I use for validation.
> Is there a "better" way to do the translation?
> --
> Walter Briscoe

From: Walter Briscoe on 4 May 2010 12:00
Thank you. That is sweet. I have 2 lists with about 20 and about 50
translations. I use the choose method for the shorter and the index
method for the latter, so I have examples of both.

In message <O4qEpk16KHA.3880(a)TK2MSFTNGP04.phx.gbl> of Tue, 4 May 2010
15:12:21 in microsoft.public.excel.programming, ozgrid.com
<dave(a)ozgrid.com> writes
>See this page for different ways.
>http://www.ozgrid.com/Excel/cell-lookup.htm
--
Walter Briscoe
 | 
Pages: 1
Prev: how can i apply a footer in every .exl file
Next: To attach file to a workbook in a addin.