I run Excel 2003 on a Windows Vista system.
I have an application in which I want to translate whole, non-negative
numbers to text. The nearest function to meet my need seems to be
OFFSET.
1234567A B C D E
A B C D E
1 Clubs Dimonds Hearts Spades NoTrump
2 0 Clubs

B2 is =OFFSET($A$1,0,$A$2,1,1)
I use 0 for cols to show the problem with the next part
If I ensure
B3 is =OFFSET({"Clubs"},0,$A$2,1,1)
I hoped to do something similar with a constant array.
I accept the error that Excel gives me because help for OFFSET says:

"OFFSET

Returns a reference to a range that is a specified number of rows and
columns from a cell or range of cells. The reference that is returned
can be a single cell or a range of cells. You can specify the number of
rows and the number of columns to be returned.

Syntax

OFFSET(reference,rows,cols,height,width)

Reference is the reference from which you want to base the offset.
Reference must refer to a cell or range of adjacent cells; otherwise,
OFFSET returns the #VALUE! error value."

Obviously, Excel does not accept an array constant as equivalent to a
cell or range of cells. ;(

I am looking for a better way of including the string constants within
formulas, rather than in cells.

I don't like
=IF($A$2=0,"Clubs",IF($A$2=1,"Dimonds",IF($A$2=2," Hearts",IF($A$2=3,"Spades","NoTrump"))))
or
=IF($A$22,IF(A2=0,"Clubs","Dimonds"),IF($A$24,IF ($A$2=2,"Hearts","Spades"),"NoTrump"))

I would value suggestions.
--
Walter Briscoe

Walter Briscoe wrote:
I run Excel 2003 on a Windows Vista system.
I have an application in which I want to translate whole, non-negative
numbers to text. The nearest function to meet my need seems to be
OFFSET.
1234567A B C D E
A B C D E
1 Clubs Dimonds Hearts Spades NoTrump
2 0 Clubs

B2 is =OFFSET($A$1,0,$A$2,1,1)
I use 0 for cols to show the problem with the next part
If I ensure
B3 is =OFFSET({"Clubs"},0,$A$2,1,1)
I hoped to do something similar with a constant array.
I accept the error that Excel gives me because help for OFFSET says:

"OFFSET

Returns a reference to a range that is a specified number of rows and
columns from a cell or range of cells. The reference that is returned
can be a single cell or a range of cells. You can specify the number of
rows and the number of columns to be returned.

Syntax

OFFSET(reference,rows,cols,height,width)

Reference is the reference from which you want to base the offset.
Reference must refer to a cell or range of adjacent cells; otherwise,
OFFSET returns the #VALUE! error value."

Obviously, Excel does not accept an array constant as equivalent to a
cell or range of cells. ;(

I am looking for a better way of including the string constants within
formulas, rather than in cells.

I don't like
=IF($A$2=0,"Clubs",IF($A$2=1,"Dimonds",IF($A$2=2," Hearts",IF($A$2=3,"Spades","NoTrump"))))
or
=IF($A$22,IF(A2=0,"Clubs","Dimonds"),IF($A$24,IF ($A$2=2,"Hearts","Spades"),"NoTrump"))

I would value suggestions.

I wonder if u.t.l is the best place to post this query?

In message
internet.com of Mon, 24 Jun 2013 15:21:45 in uk.transport.london,
Recliner writes
Walter Briscoe wrote:
I run Excel 2003 on a Windows Vista system.
I have an application in which I want to translate whole, non-negative
numbers to text. The nearest function to meet my need seems to be
OFFSET.

[snip]

I would value suggestions.

I wonder if u.t.l is the best place to post this query?


OOPS in spades.
Thanks for the restraint in your response.
I should have posted to microsoft.public.excel.worksheet.functions.
--
Walter Briscoe