« Counting letters in a string | Main | Largest values in an array »
March 08, 2005
Advanced Functions: INDIRECT
The INDIRECT function converts a string into a cell reference. With examples, I will show how useful this can be in certain applications.
D3:D6 is an array of numbers. The values in B3:B6 will be used in the different INDIRECT examples.
B10 =SUM(INDIRECT(B5)) This is equivalent to =SUM(D3:D6)
B12 =SUM(INDIRECT("D"&B3&":D"&B4)) A more elaborate example of assembling a string from different sources to be converted by INDIRECT to a range reference. B3 and B4 contain the start and end rows respectively of the array wewish to SUM
B14 =INDIRECT(ADDRESS(3;4;4)) The ADDRESS function also can be combined with INDIRECT. ADDRESS takes row and column numbers as arguments - converting them to a string - just what INDIRECT needs.
B15 =SUM(INDIRECT("Sheet"&B6&"."&B5)) An indirect reference to another sheet in the same document.
The one major difference between OOo Calc and Excel with regard to the INDIRECT function is the handling of named ranges. Excel allows you to make an indirect reference to a named range. This is not the case with OOo Calc.
Posted by Dave at March 8, 2005 04:36 AM
Comments
Have you read the article from pcmage.com? Here is the link, http://www.pcmag.com/article2/0,1759,1625218,00.asp with the title of the article "Use a Formula as a Named Range in Excel" is this possible to do in OOo Calc? Any insight would be greatly appreciated. Have a good one.
Will
Posted by: Will at September 1, 2005 01:23 PM
Hi,
Why won't this formula work when entered in a 12 row array? It will return the first value if entered in a single cell.
=INDIRECT(ADDRESS(7,COLUMN(),,,"Budget")&":"&ADDRESS(18,COLUMN()))
The ADDRESS formulae return this ...
{"Budget!$F$7:$F$18"}, with the curly brackets, whic the INDIRECT function doesn't like.
Regards,
ADRIAN
Posted by: Adrian at December 7, 2005 09:05 PM