Why create a function like ADDRESS when it can't be then used immediately in
other functions (this would seem the whole purpose).

Eg. =CELL("contents",ADDRESS(O1,13,4)) doesn't work.

How lame is the 'talent' programming Excel?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

themantheworldlovesmorethannathan wrote...
Why create a function like ADDRESS when it can't be then used immediately in
other functions (this would seem the whole purpose).

Eg. =CELL("contents",ADDRESS(O1,13,4)) doesn't work.

How lame is the 'talent' programming Excel?
....

Less lame than those who don't know how to use Excel.

Obviously you don't know what the ADDRESS and CELL functions do. All
the ADDRESS function does is return a TEXT STRING representing a cell
address. Text strings are NOT cell addresses themselves. Otherwise it'd
be impossible to use formulas like

="A1"&" Brands"

to create the text result "A1 Brands" because Excel would always be
trying to interpret "A1" as the cell reference A1. The double quotes
make all the difference.

If you want to treat the result of the ADDRESS function as a cell
reference, use it as the argument to the INDIRECT function. INDIRECT
takes text that looks like range addresses and converts them into
references to those ranges. For example, INDIRECT("A1") evaluates to
the same thing as A1. However, since A1 is much quicker to type than
INDIRECT("A1") and more efficient as well (read up about volatile
functions), there's never a good reason to use INDIRECT("A1").
Likewise, there's never a good reason to use INDIRECT(ADDRESS(...)).

Since you want your ADDRESS call above treated like a cell reference,
use INDEX(1:65536,O1,13) instead, or use the shorter INDEX($M:$M,O1).

As for CELL, the contents property is misnamed (blame Lotus Development
Corp for the original usage and Microsoft for choosing strict 123
compatability). It actually returns the cell's value. You don't need
CELL("contents",...) for that. Just refer to the cell. IOW, you could
replace your formula above with

=INDEX($M:$M,O1)

Eg. =CELL("contents",ADDRESS(O1,13,4)) doesn't work.

It is not a valid function call. Address does not accept O1. Also, the
second parameter of CELL is a range reference, not a text address of a range.
You could use INDIRECT to remedy that, but it's still unclear what you're
trying to do.

If you're trying to refer to a cell using just row/col reference, where D3
contains 1 (row number), E3 contains 15 (column number), either of these
would return the contents of O1.
=OFFSET(INDIRECT("A1"),D3-1,E3-1)
=INDIRECT(ADDRESS(D3,E3))

Considering Excel's ability to nest functions, use array functions, use VBA
to write your own functions, use VBA to manipulate data with Word/Access and
other programs through cross application programming, use VBA to create
custom objects through class modules, create custom forms, allow access to
the file system through VBA, and access windows API functions - I, for one,
would say the 'talent' behind Excel's programmming is considerable.


"themantheworldlovesmorethannathan" wrote:

Why create a function like ADDRESS when it can't be then used immediately in
other functions (this would seem the whole purpose).

Eg. =CELL("contents",ADDRESS(O1,13,4)) doesn't work.

How lame is the 'talent' programming Excel?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

the 'talent' behind Excel's programmming is considerable.

But not so considerable that they can come up with a function like:

=CONCATENATE(A1:A10,",")

Or, to keep Excel (2002 XP) from crashing when I use the evalaute formula
menu command on such a simple formula as:

=IF(A10,(MATCH(TRUE,A2:A$100,0)-1)*A1,"")

Biff

"JMB" wrote in message
...
Eg. =CELL("contents",ADDRESS(O1,13,4)) doesn't work.

It is not a valid function call. Address does not accept O1. Also, the
second parameter of CELL is a range reference, not a text address of a
range.
You could use INDIRECT to remedy that, but it's still unclear what you're
trying to do.

If you're trying to refer to a cell using just row/col reference, where D3
contains 1 (row number), E3 contains 15 (column number), either of these
would return the contents of O1.
=OFFSET(INDIRECT("A1"),D3-1,E3-1)
=INDIRECT(ADDRESS(D3,E3))

Considering Excel's ability to nest functions, use array functions, use
VBA
to write your own functions, use VBA to manipulate data with Word/Access
and
other programs through cross application programming, use VBA to create
custom objects through class modules, create custom forms, allow access to
the file system through VBA, and access windows API functions - I, for
one,
would say the 'talent' behind Excel's programmming is considerable.


"themantheworldlovesmorethannathan" wrote:

Why create a function like ADDRESS when it can't be then used immediately
in
other functions (this would seem the whole purpose).

Eg. =CELL("contents",ADDRESS(O1,13,4)) doesn't work.

How lame is the 'talent' programming Excel?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I agree that the concatenate function would be a good idea, however, you have
the ability to write a UDF to perform that function - so although it's
slower, it can be done. I never said it was perfect (no application is),
just that I thought it took some ability to put it together.

With regards to the OP, Excel may very well be able to do what he wants, but
he's not putting the functions together properly (and there may very well be
a better way of doing by just linking the cells or using Index or Match - I'm
not following why he wants to use either CELLS or ADDRESS).


"Biff" wrote:

the 'talent' behind Excel's programmming is considerable.

But not so considerable that they can come up with a function like:

=CONCATENATE(A1:A10,",")

Or, to keep Excel (2002 XP) from crashing when I use the evalaute formula
menu command on such a simple formula as:

=IF(A10,(MATCH(TRUE,A2:A$100,0)-1)*A1,"")

Biff

"JMB" wrote in message
...
Eg. =CELL("contents",ADDRESS(O1,13,4)) doesn't work.

It is not a valid function call. Address does not accept O1. Also, the
second parameter of CELL is a range reference, not a text address of a
range.
You could use INDIRECT to remedy that, but it's still unclear what you're
trying to do.

If you're trying to refer to a cell using just row/col reference, where D3
contains 1 (row number), E3 contains 15 (column number), either of these
would return the contents of O1.
=OFFSET(INDIRECT("A1"),D3-1,E3-1)
=INDIRECT(ADDRESS(D3,E3))

Considering Excel's ability to nest functions, use array functions, use
VBA
to write your own functions, use VBA to manipulate data with Word/Access
and
other programs through cross application programming, use VBA to create
custom objects through class modules, create custom forms, allow access to
the file system through VBA, and access windows API functions - I, for
one,
would say the 'talent' behind Excel's programmming is considerable.


"themantheworldlovesmorethannathan" wrote:

Why create a function like ADDRESS when it can't be then used immediately
in
other functions (this would seem the whole purpose).

Eg. =CELL("contents",ADDRESS(O1,13,4)) doesn't work.

How lame is the 'talent' programming Excel?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

JMB wrote...
Eg. =CELL("contents",ADDRESS(O1,13,4)) doesn't work.

It is not a valid function call. Address does not accept O1. . . .
....

O1 is a cell address, so ADDRESS does accept it. It'd have to be an
integer between 1 and 65536 for ADDRESS to return a nonerror result,
but ADDRESS would accept it even if the value of O1 were something else.

For some odd reason my mind was thinking text "O1" -I was making assumptions
about what he's trying to do (I'll spare you the explanation). Thanks for
correcting me -obviously my mind is slipping.

But Cell still doesn't play nice w/Address w/o using Indirect -so why
attempt to use it all (versus Index - I think you already posted this). Why
take a shot at the programmers when you don't put the formula together
correctly?


"Harlan Grove" wrote:

JMB wrote...
Eg. =CELL("contents",ADDRESS(O1,13,4)) doesn't work.

It is not a valid function call. Address does not accept O1. . . .
....

O1 is a cell address, so ADDRESS does accept it. It'd have to be an
integer between 1 and 65536 for ADDRESS to return a nonerror result,
but ADDRESS would accept it even if the value of O1 were something else.