Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
address function
L1 has the formula
=ADDRESS(4,MATCH(L$2,$3:$3,0)) This returns $g$4 L2 has the value 08 Range d3 €“ j3 has the values D e f g h I j Row 3 07,07,07,08,08,08,08 Row 4 23,22,21,23,21,21,22 L4 as the formula =SUM(OFFSET(INDIRECT(L$1),0,0,1,COUNTIF($3:$3,L$2) )) This returns 87 This is all very well. When I try to replace the indirect portion of L4 with the address formula from L1 it errors. =SUM(OFFSET(ADDRESS(4,MATCH(L$2,$3:$3,0)),0,0,1,CO UNTIF($3:$3,L$2))) ????? Any ideas??? Peter |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
address function
That's because the ADDRESS function returns a TEXT string. A TEXT string is
not a valid reference even if it looks like one. -- Biff Microsoft Excel MVP "peter" wrote in message ... L1 has the formula =ADDRESS(4,MATCH(L$2,$3:$3,0)) This returns $g$4 L2 has the value 08 Range d3 - j3 has the values D e f g h I j Row 3 07,07,07,08,08,08,08 Row 4 23,22,21,23,21,21,22 L4 as the formula =SUM(OFFSET(INDIRECT(L$1),0,0,1,COUNTIF($3:$3,L$2) )) This returns 87 This is all very well. When I try to replace the indirect portion of L4 with the address formula from L1 it errors. =SUM(OFFSET(ADDRESS(4,MATCH(L$2,$3:$3,0)),0,0,1,CO UNTIF($3:$3,L$2))) ????? Any ideas??? Peter |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
address function
Why would you want to replace the concise "Indirect(L1)" with the much
longer Address() formula? Are you looking to eliminate the need for using cell L1, or are you just looking at this as a topic of conversation? To eliminate the need for L1, simply wrap the Address formula in Indirect, as you did previously with the "Indirect(L1)" reference: =SUM(OFFSET(INDIRECT(ADDRESS(4,MATCH(L$2,$3:$3,0)) ),0,0,1,COUNTIF($3:$3,L$2))) I would suspect that you are aware of this, since you did use Indirect with L1 in the first place. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "peter" wrote in message ... L1 has the formula =ADDRESS(4,MATCH(L$2,$3:$3,0)) This returns $g$4 L2 has the value 08 Range d3 - j3 has the values D e f g h I j Row 3 07,07,07,08,08,08,08 Row 4 23,22,21,23,21,21,22 L4 as the formula =SUM(OFFSET(INDIRECT(L$1),0,0,1,COUNTIF($3:$3,L$2) )) This returns 87 This is all very well. When I try to replace the indirect portion of L4 with the address formula from L1 it errors. =SUM(OFFSET(ADDRESS(4,MATCH(L$2,$3:$3,0)),0,0,1,CO UNTIF($3:$3,L$2))) ????? Any ideas??? Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
address function | Excel Discussion (Misc queries) | |||
cannot nest address function into another function | Excel Discussion (Misc queries) | |||
address function | Excel Discussion (Misc queries) | |||
address function | Excel Worksheet Functions | |||
ADDRESS Function | Excel Worksheet Functions |