Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: ADDRESS cannot be used in CELL. How stupid is this?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: ADDRESS cannot be used in CELL. How stupid is this?
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: ADDRESS cannot be used in CELL. How stupid is this?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: ADDRESS cannot be used in CELL. How stupid is this?
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: ADDRESS cannot be used in CELL. How stupid is this?
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: ADDRESS cannot be used in CELL. How stupid is this?
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: ADDRESS cannot be used in CELL. How stupid is this?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Can I copy a table from Word into one cell in Excel without losing data? (Office 97) | Excel Discussion (Misc queries) | |||
Format a Cell in Excel as an IP address field | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |